CODEWITHSUNDEEP
mysql
CaymanCarver
CaymanCarver

Reputation: 419

MySQL query for records without certain related records

Lots of customers. Some have invoices. Some have meta records showing which email lists they belong to.

I need to find customers who do NOT have invoices within 30 days (but it's OK if they have no invoices at all, or older invoices) and who do NOT have one particular email meta record (it's OK if they have no meta records at all, or other types of meta records).

The nearest I can figure this query out (in plain English) would be a "not having" condition:

select * from customers
left join invoices on …
and also left join meta on …
NOT HAVING invoice_date > 30 days ago AND
NOT HAVING meta_value=unwanted_list

Ugh. Anyone?

Upvotes: 1

Views: 31

Answers (2)

mwatzer
mwatzer

Reputation: 864

Like that? 

SELECT * FROM customers
LEFT JOIN invoices ON...
LEFT JOIN meta ON...
WHERE invoice_date < NOW() - INTERVAL 30 DAY
AND meat_value NOT LIKE...

Upvotes: 0

Shadow
Shadow

Reputation: 34294

Left join is a good idea, but you have to use subqueries. The other solution is to use not exists subqueries:

select c.* from customers c
where not exists (select 1 from invoices customer_id=c.id and invoice_date>=curdate()-30)
and not exists (select 1 from meta_value where customer_id=c.id and meta_value=...)

Upvotes: 1

Related Questions