problem creating "Unmatched" query

[barbarafloyd]

Hi,

Im Using Access 2003 and I am pretty new to SQL and am struggling to understand why my query wont work. I have 2 tables and I want to return only those rows from the first table where the joint primary key is not in the second.
My code is below, and it returns 0 rows even though I know there are 2 rows in XP Product List table that have a different "set" of 3 fields that make up the primary key.
*boggle*
thanks very much for your help!


SELECT *
FROM [XP Product list]
WHERE NOT EXISTS
(SELECT 1
FROM ([XP Product list] INNER JOIN [MAIN CBA VALIDATION]
ON [XP Product list].Application = [MAIN CBA VALIDATION].Application
AND [XP Product list].Type = [MAIN CBA VALIDATION].Type
AND [XP Product list].[Packaging Status] = [MAIN CBA VALIDATION].[Packaging Status])
WHERE [XP Product list].Type = "SWAB"
AND [XP Product list].[Packaging Status] Like "Comp*" )

 

[BNPMike]

It's easier and more efficient to do this a different way.

Join the two tables with a left outer join. Then select the rows where the key is null in the joined-to table. These will be the ones where there is no matching row in the joined-to table.

 

[barbarafloyd]

Hiya,

Thanks for the tip, Ive done what you said and got it to work, thats fantastic!
If anyone knows what is wrong with my original post let me know though, as i still cant get my head around why it wouldnt work!

Thanks!
Barbara

 

[JonFer]

First - I think Access wants you to use "Select * from..." with Exists/Not Exists.

Second - You need to alias the instance of [XP Product list] within the Not Exists so you can refer to fields in the instance of [XP Product list] in the main Select. Right now, your Not Exists Select is generating the same record(s) regardless of which record the main Select is processing. So as long as there is one matching row between the two tables, the Not Exists condition will always be False for every row examined.

John

http://www.interautomateconsulting.com

 

[cheerio]

IF I were tempted to use an EXISTS construction it would be more like:

SELECT *
FROM [XP Product list]
WHERE NOT EXISTS
(
SELECT *
FROM [MAIN CBA VALIDATION]
WHERE
[XP Product list].Application = [MAIN CBA VALIDATION].Application
AND
[XP Product list].Type = [MAIN CBA VALIDATION].Type
AND
[XP Product list].[Packaging Status] = [MAIN CBA VALIDATION].[Packaging Status]
)
WHERE [XP Product list].Type = "SWAB"
AND [XP Product list].[Packaging Status] Like "Comp*

 

[barbarafloyd]

hiya!

Thanks to everyone for their help, this is a great site!!

Cheers
Barbara