MySQL

Exclusionary SELECT in SQL

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2005

Posts: 14

Reputation: aiden is an unknown quantity at this point

Solved Threads: 0

aiden

Offline

Newbie Poster

Exclusionary SELECT in SQL

Mar 8th, 2006

Hi,

While I'm not entirely new with SQL, I am not by any means highly knowledgable.

I'm attempting to write a SQL statement to do the following, and having no luck:

Assumptions:
There is only one table, with several fields of metadata including the following.

1 field (integer) that indicates which section the table element is in. (e.g., 1="Illustrations Gallery" 2="Painting Gallery") Call this Field A.

1 field that can be NULL, or can contain the id (pkey) of another entry in the same table indicating a link between them. Call this Field B.

Field B can only contain links (primary keys) from elements that have a specific value of Field A.

I want to select the IDs of "eligable" elements. That is, rows who have the right value of A, and are not present in any other row's value of B.

Therefore: I need a query that says, essentially -

SELECT * from `table` where `A`=X AND `id`=(Doesn't Exist Anywhere in B);

Any ideas?

--
Aiden

•

Join Date: Jun 2005

Posts: 354

Reputation: Troy is an unknown quantity at this point

Solved Threads: 5

Troy

Offline

Posting Whiz

Re: Exclusionary SELECT in SQL

Mar 10th, 2006

What version of MySql? 4.1 and newer support subqueries. If your version support subqueries, you can do something like this:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT *
FROM mytable
WHERE a = 147
AND id NOT IN (SELECT b FROM mytable)
select *
from mytable
where a = 147
and id not in (select b from mytable)

Otherwise, try this:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT t1.*
FROM mytable t1
LEFT JOIN mytable t2 ON t1.id = t2.b
WHERE t1.a = 147
AND t2.b IS NULL
select t1.*
from mytable t1
LEFT JOIN mytable t2 ON t1.id = t2.b
where t1.a = 147
and t2.b IS NULL