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.
Selecting Records Without Corresponding Records?
Moderator: General Moderators
- aaronhall
- DevNet Resident
- Posts: 1040
- Joined: Tue Aug 13, 2002 5:10 pm
- Location: Back in Phoenix, missing the microbrews
- Contact:
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):
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)