Need help with Select in two tables, find sample not in one 1

[DougP]

I have two tables each has Project# and Sample Number
Need to find which samples are not in first table.
here is my botched SQL

SELECT [ChainOfCustody Details].[Project Number], [ChainOfCustody Details].SampleNum, SamplesChecked.Sample
FROM SamplesChecked INNER JOIN [ChainOfCustody Details] ON (SamplesChecked.[Project Number] = [ChainOfCustody Details].[Project Number]) AND (SamplesChecked.Sample = RIGHT( [ChainOfCustody Details].SampleNum,4)) AND  SamplesChecked.Sample NOT IN (Select RIGHT([ChainOfCustody Details].SampleNum,4) From [ChainOfCustody Details])

DougP
< I love mine

http://www.robots2000.com

 

[lespaul]

typed not tested:

SELECT * FROM
Table2 2
LEFT OUTER JOIN table1 1 on 2.[project number] = 1.[project number] and 2.samplenum = 1.samplenum
where 1.samplenum is null

Leslie

Have you met Hardy Heron?

 

[DougP]

OK after tweaking I have this

SELECT [ChainOfCustody Details].[Project Number], [ChainOfCustody Details].SampleNum FROM SamplesChecked  
LEFT OUTER JOIN [ChainOfCustody Details] ON (SamplesChecked.Sample = [ChainOfCustody Details].SampleNum) AND (SamplesChecked.[Project Number] = [ChainOfCustody Details].[Project Number])
where  [ChainOfCustody Details].samplenum is null

It returns 19 records with blank fields
There are 19 records in the table with less records (SamplesChecked) There are 22 records in [ChainOfCustody Details] so I would like to see the 3 records not in SamplesChecked.

DougP
< I love mine

http://www.robots2000.com

 

[PHV]

What about this ?

SELECT D.[Project Number], D.SampleNum
FROM SamplesChecked C
RIGHT JOIN [ChainOfCustody Details] D ON C.[Project Number] = D.[Project Number] AND C.Sample = Right(D.SampleNum,4)
WHERE C.Sample Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

PHV, you're nothing but amazing at this. Thanks a million.
I needed to add the project number to narrow down the results.

SELECT D.SampleNum
FROM SamplesChecked AS C RIGHT JOIN [ChainOfCustody Details] AS D ON C.[Project Number]=D.[Project Number]
WHERE (((D.[Project Number])="080448") AND ((C.Sample) Is Null));

DougP
< I Built one

http://www.robots2000.com

 

[DougP]

Actually after more testing it does NOT work.
It just returns all the matches in ChainOfCustody Details instead of the differences


DougP
< I Built one

http://www.robots2000.com

 

[PHV]

Why did you change the ON clause ????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

Dooh!!!
I see now what happened. The project I was testing I just finished so all the samples were there in the SamplesChecked table and the query returned nothing. not knowing why I started messing with it. I'm Such as doofus. So now to test it I deleted a few samples from the SamplesChecked table.
Anyway I just copied your code back again and added the project# and now it works.

SELECT D.[Project Number], D.SampleNum
FROM SamplesChecked AS C RIGHT JOIN [ChainOfCustody Details] AS D ON (C.Sample = D.SampleNum) AND (C.[Project Number] = D.[Project Number])
WHERE (((D.[Project Number])="080446") AND ((C.Sample) Is Null));

DougP
< I Built one

http://www.robots2000.com