query to return records which do not contain an item

  • Thread starter Thread starter CrumpetHead
  • Start date Start date
C

CrumpetHead

I have three tables, joined as follows;

A 1-------m B m------1 C

I need to return records in A, which do not contain a specific record in C.
Each record in A for which this is true should only be returned once.

A real world example might be STUDENT 1-----m ENROLMENT m----1 SUBJECT
What students are not enrolled in music?

Any help would be appreciated.

Regards,
Paul
 
I have three tables, joined as follows;

A 1-------m B m------1 C

I need to return records in A, which do not contain a specific record in C.
Each record in A for which this is true should only be returned once.

A real world example might be STUDENT 1-----m ENROLMENT m----1 SUBJECT
What students are not enrolled in music?

Any help would be appreciated.

Regards,
Paul
Click to expand...

The somewhat obscure NOT EXISTS() clause can help here:

SELECT * FROM A
WHERE NOT EXISTS
(SELECT B.A_ID FROM B INNER JOIN C ON B.C_ID = C.ID
WHERE C.SUBJECT = "Music" AND B.A_ID = A.A_ID)
 
Back
Top