Page 1 of 1

Selecting Records Without Corresponding Records?

Posted: Thu Jan 18, 2007 4:55 pm
by cfytable
I have three tables:

tbl_publication
============
pubID
pubName

tbl_subject
================
subjectID
subjectName

tbl_publication_subject
=================
pubsubjID
pubID
subjectID

I don't have foreign keys setup, as I'm using an older version of mYSQL.

Basically, only some of the publications will appear within tbl_publication_subject. What I would like to do is select all those pubIDs from tbl_publication whose pubIDs *do not appear* in tbl_publication_subject. Is this possible? Can somebody help with the SQL? This is what I have so far:

SELECT tbl_publication.pubID, tbl_publication.pubName FROM tbl_publication, tbl_publication_subject WHERE (tbl_publication.pubID != tbl_publication_subject.pubID)

Thanks in advance for your help.

Posted: Thu Jan 18, 2007 6:14 pm
by aaronhall
Just a comment about your design: you could add a subjectID column to tbl_publication and drop tbl_publication_subject altogether.

Anyway, this should work (may not be the best way to do it, though, and untested):

Code: Select all

SELECT * FROM tbl_publication tp
  LEFT JOIN tbl_publication_subject tps
  ON tp.pubID = tps.pubID
WHERE ISNULL(tps.pubID)