Basic Query Design Question

[MrTBC]

Access 2000.
Sorry I'm sure this is a really simple one but I'm stumped.
If you have two tables joined via a one-many relationship, how can you design a query to show only the records in the 'One' table which DO NOT have a corresponding entry in the 'Many' table.
e.g. Products that have NOT been ordered


Thanks very much.

 

[PHV]

Something like this ?
SELECT A.* FROM parentTable A LEFT JOIN childTable B ON A.PK = B.FK
WHERE B.FK Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

One solution:

SELECT * FROM PRODUCTS WHERE PRODUCTID NOT IN (SELECT DISTINCT PRODUCTID FROM ORDERS)

Leslie

 

[MrTBC]

Aaaaah - that easy. Thank you.