LEFT JOIN help

[holstead]

Customer_local (Customer_ID tinyint primary key auto_increment NOT NULL, FName tinytext);

xref (Customer_ID tinyint, email_ID tinyint);

I want to select FName from Customer_local where they DO NOT have an entry for Email_ID 1,2, or 3 in the table xref. When an email is sent to a customer, the customer id and email # is stored in the xref table.

Example if 2 emails (email_ID 1 and 2)were sent to misc customers:

Customer_local

Customer_ID - FName

101 - John
102 - Jack
103 - Jason

xref

Customer_ID - email_ID

101 - 1
102 - 1
103 - 1
101 - 2
102 - 2

Therfore, Jason is the only customer who has not received email_ID 2. How do I query this? What if I want to find all customers who have not received emails 1, 5, 6, 12, etc..?

Here's what I have sofar, it displayes everyone unfortunatly:

SELECT Customer_local.Customer_ID, Customer_local.FName from Customer_local LEFT JOIN xref ON xref.Customer_ID = Customer_local.Customer_ID AND ( xref.email_ID = 1 OR xref.email_ID = 2 OR xref.email_ID = 3 );

[ultraslacker]

I want to select FName from Customer_local where they DO NOT have an entry for Email_ID 1,2, or 3 in the table xref. When an email is sent to a customer, the customer id and email # is stored in the xref table.

You would use an OUTER JOIN but with a WHERE limiting results unmatched rows.

SELECT Customer_local.Customer_ID, Customer_local.FName from Customer_local
LEFT JOIN xref ON xref.Customer_ID = Customer_local.Customer_ID AND xref.email_ID = 1
LEFT JOIN xref x2 ON x2.customer_id = customer_local.customer_id AND x2.email_id = 1
WHERE xref.email_id IS NULL OR x2.email_id IS NULL...