Exclusionary SELECT in SQL

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

Newbie Poster

14 posts since Jun 2005

Reputation Points: 10

Solved Threads: 0

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

Posting Whiz

362 posts since Jun 2005

Reputation Points: 36

Solved Threads: 6

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

The Queen of DaniWeb

Administrator

19,427 posts since Feb 2002

Reputation Points: 1,474

Solved Threads: 230

This article has been dead for over three months

You