Howto select A.* where A.id not in B.id, and A.email not in B

[swinstead]

Hi,

 

I'm not sure if this is beyond what I can do with one SQL statement but it's worth asking, because maybe some hardened SQLers can provide some insight.

 

Basically for our site we have a customers table, and an orders table, featuring all the columns you'd expect to go with each i.e

 

customers

--

customer_id

name

email

tel

etc

 

orders

--

order_id

customer_id

product_id

etc

 

 

I'm trying to select every customer who has registered with us but never bought anythign from us i.e they have a record in customers table, but there's no record of their customer_id in the orders table.

 

So far I've done that, using this:

 

select * from customers left join orders on customers.customer_id = orders.cust_id where orders.cust_id is NULL

 

But there's a twist. Through a design flaw I inherited, there can be multiple customer accounts using the same email address. So a user could have registered with us twice, but if only one account has purchased then this query would show them as having never bought anything.

 

What I want to do is filter out from my result set any customers that have a record in the orders table, or the matching email address has a record in the customers table. The email isn't logged in the orders table so I would have to do some sort of join on the customer id and the email, would i not? It's a little over my head to be honest.

 

Is this possible or am I expecting too much from an SQL query? Would it be easier to do the query I have above then do another query on the resulting data>

[fenway]

Sounds like what you need to do is get a list of customer IDs regardless of "origin" in a derived table, and then join that to orders.