select fields from a table that don't exist in another table

[Alex]

I have feilds Product_ID and TerritoryID in the both tables Table1 and Table2.
Product_ID and TerritoryID is a unique combination.

I'm traying to create a query to get rows from Table1 that don't exist in
the Table2.
For the one field it's easy:
SELECT *
FROM Table1
WHERE (Table1.Product_ID Not In (SELECT Table2.Product_ID FROM Table2));

How could I do it for the both fields to get rows where Table1.Product_ID
and Table1.TerritoryID don't exist in Table2?

Thanks

 

[Michel Walsh]

Hi,

SELECT ... FROM...
WHERE NOT EXISTS ( SELECT * FROM table2 WHERE table1.Product_ID
=table2.Product_ID AND table1.TerritoryID=table2.TerritoryID)



but and outer join would be faster:


SELECT a.*
FROM table1 AS a LEFT JOIN table2 As b
ON a.Product_ID =b.Product_ID AND a.TerritoryID=b.TerritoryID
WHERE b.ProductID IS NULL



Hoping it may help,
Vanderghast, Access MVP