Exclusionary SELECT in SQL

Expand Post »

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

Similar Threads

Error in creating ODBC connection."Reason: Not assosiated with a trusted SQL Server" in ASP
Best way to develop db for survey? in Database Design
Hi,Need help on sql select statement structure in MS SQL
Command text was not set for the command object. in ASP
select statemt LIKE in MySQL

aiden

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Jun 2005

Permalink

Mar 10th, 2006

Re: Exclusionary SELECT in SQL

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

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:

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

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

Troy

Reputation Points: 36

Solved Threads: 6

Posting Whiz

Offline

354 posts
since Jun 2005

Permalink

Mar 10th, 2006

Re: Exclusionary SELECT in SQL

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

cscgal

Administrator

Staff Writer

Reputation Points: 1422

Solved Threads: 162

The Queen of DaniWeb

Offline

13,645 posts
since Feb 2002

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: query_cache_size

Next Thread in MySQL Forum Timeline: Problem with create script

DaniWeb Message

Cancel Changes

User Name
Password