Posted by Jerry Stuckle on 10/21/05 20:24

Alex Stuy wrote:
> Jerry Stuckle <jstucklex@attglobal.net> wrote in
> news:yLWdnceqTOVOKcjeRVn-tQ@comcast.com:
>
>
>>Alex Stuy wrote:
>>
>>>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
>>>
>>>
>>>thanks,
>>> A
>>>
>>
>>What version of MySQL?
>>
>>
>
>
> Sorry, should have mentioned MySql version 3.23.

OK, 3.2.3 doesn't have subqueries, so your job is a little harder.

I think Thomas has the best idea. If you can get up to MySQL 5.1, you
can use subqueries such as

SELECT Specimens.*
FROM Specimens
WHERE RecordID NOT IN
(SELECT SpecimenRecordID FROM Verifications)

Or something similar.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]