Query Help - Find records with a 'missing' value from a table

[wob]

I'm sure this is an easy one for someone, but I can't figure it out!

I've got an address table, that includes customer_no and address_type fields.
Address_type can be several values, 'Home', 'Mail', 'Other', 'Business' and a few others.

The table is like this:

customer_no, address_type
1, Home
1, Mail
2, Home
2, Business
3, Other
4, Mail
4, Business

Note that customers can have more than one address set up.
How do I select customers that I have no 'Home' addresses for? (ie, customers 3 and 4)

 

[Joe4]

It can be done using a Subquery. Basically, create a query that returns the Customer Numbers where the type is "HOME". Then, create a query that uses that as part of the criteria, returning records that are not found in that Subquery. Using DISTINCT will combine all the duplicate returns.

So, assuming your table name is "CustomerTable", that SQL code would look something like:

SELECT DISTINCT CustomerTable.customer_no
FROM CustomerTable
WHERE CustomerTable.customer_no Not In
(SELECT CustomerTable.customer_no
FROM CustomerTable
WHERE CustomerTable.address_type="Home");

 

[wob]

Perfect - many thanks!