Exclusionary SELECT in SQL

[aiden]

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

[Troy]

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

select *
from mytable
where a = 147
and id not in (select b from mytable)

Otherwise, try this:

select t1.*
from mytable t1
LEFT JOIN mytable t2 ON t1.id = t2.b
where t1.a = 147
and t2.b IS NULL

That second query was not fun! I got help at this URL:
http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html

[Dani]

The second query is the way I am used to doing it.