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:

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?


12 Years
Discussion Span
Last Post by Dani

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:

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.