Checking if column combinations exist in another table

[blacksaibot]

For simplicity I will use generic names



Table1 and Table2 both have an ID and Description columns. However, Table2 calls its ID column XY

I want to see if the combination of ID and Description from Table1 does not exist in Table2

I know I can do it with one column by saying SELECT [ID] FROM [Table1] WHERE [ID] NOT IN (SELECT DISTINCT [XY] FROM [Table2])

How can I write the query where it's something like this:

SELECT [ID], [Description] FROM [Table1] WHERE [ID] NOT IN (SELECT DISTINCT [XY], [Description] FROM [Table2])

Will it know to match up ID with XY, and look to compare the pair ID, Description with XY, Description?

I tried that and it gave some error about using EXISTS, so I tried to say "NOT EXISTS" and I get a syntax error

[blacksaibot]

Something screwy is going on, because when I do the simple query of NOT IN on [Description] no records are returned, even though I can see with my own eyes that there are Descriptions that do not exist.

Can Access not perform a query like that on a Text field or something?

[JoeM]

An unmatched query should do it (joining on two fields)

Try this:

SELECT Table1.ID, Table1.Description
FROM Table1
LEFT JOIN Table2
ON Table1.ID = Table2.XY
AND Table1.Description = Table2.Description
WHERE Table2.XY Is Null;

[JoeM]

To do kind of like the method you were trying, you could also use this code:

SELECT Table1.ID, Table1.Description
FROM Table1
WHERE (Table1.ID & Table1.Description)  Not In
(SELECT (Table2.XY & Table2.Description)
FROM Table2);

[blacksaibot]

An unmatched query should do it (joining on two fields)

Try this:

SELECT Table1.ID, Table1.Description
FROM Table1
LEFT JOIN Table2
ON Table1.ID = Table2.XY
AND Table1.Description = Table2.Description
WHERE Table2.XY Is Null;

Thanks! Just tried it and it works.