Posted by Thomas Bartkus on 10/19/05 17:16

"Alex Stuy" <astuy@spammenot-bio.fsu.edu> wrote in message
news:Xns96F3E0ECB258astuyspammenotbiofsu@216.196.97.131...
>
> Hi,
>
> I'm stumped on what seemed like a simple query. Can someone help me
> with a query that will select all rows from table Specimens where
> Specimens.RecordID does not occur in Verifications.SpecimenRecordID
> collumn? (ie, rows 1,5,6)
>
>
> Table:Specimens
> RecordID Barcode
> 1 00000001
> 2 10101010
> 3 11101010
> 4 10111101
> 5 10111011
> 6 11110111
>
> Table:Verifications
> RecordID SpecimenRecordID VerificationDate VerifierRecordID
> 1 2 12/4/2005 4
> 2 4 4/14/2000 4
> 3 3 7/31/1997 5
> 4 2 5/23/1999 7
>

SELECT Specimens.*
FROM Specimens
LEFT JOIN Verifications ON
(Specimens.RecordID=Verifications.SpecimenRecordID)
WHERE (Verifications.RecordID IS Null)

RecordID Barcode
1 00000001
5 10111011
6 11110111

By using Specimens as the base table together with LEFT JOIN on
Verifications, you are pulling up all RecordID(s) from Specimens. LEFT JOIN
Verifications *trys* to place a matching SpecimenRecordID. If one cannot be
found, it puts a Null to stand in for the missing value.
You then use WHERE to restrict to records for which Verifications.RecordID
came back at you as null.

I'm guessing you probably don't need that first column RecordID. You might
consider renaming it SpecimenID in table Specimens (instead of RecordID).
Then you would only need a corresponding SpecimenID field in Verifications
to do your match up. This yields a less puzzling query. Matching along
identically named fields that appear in 2 different tables seems to cause
less brain damage - in my case anyway :-)

SELECT Specimens.*
FROM Specimens
LEFT JOIN Verifications ON
(Specimens.SpecimenID=Verifications.SpecimenID)
WHERE (Verifications.SpecimenID IS Null)

and you get to drop an uneccessary column (RecordID) from Verifications.

Thomas Bartkus

[Back to original message]