Selecting Records Without Corresponding Records?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cfytable
Forum Commoner
Posts: 29
Joined: Thu May 12, 2005 3:36 pm

Selecting Records Without Corresponding Records?

Post 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.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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)
Post Reply