Select customers with no invoices after date

user_3759368 0
Viewed 312

I have Client and Invoice tables. They have one-to-many relationship, where Client.id = Invoice.client_id.

Client columns:

  • id

Invoice columns:

  • id,
  • client_id,
  • invoice_date

Of course the example is simplified to relevant data.

I am trying to select customers who did NOT have invoices after '2010-01-01'.

I can't figure out any working way to do this. Some routes I took look like this (there many other variations, but no point displaying the here):

SELECT c.id, COUNT(i.invoice_date > "2010-01-01") AS cnt 
 FROM Client AS c LEFT JOIN Invoice i ON i.client_id = c.id 
 GROUP BY c.id HAVING cnt = 0

and

SELECT client_id, COUNT(invoice_date > '2010-01-01') as cnt 
FROM Invoice
GROUP BY client_id HAVING cnt = 0
mysql
3 Answers

You can use a sub-query with NOT EXISTS like this:

SELECT *
FROM Client
WHERE NOT EXISTS (
  SELECT 1
  FROM Invoice 
  WHERE Invoice.invoice_date > '2010-01-01' AND Invoice.client_id = Client.id
)

You can also use SUM with CASE or IF:

-- CASE
SELECT c.id, SUM(CASE WHEN i.invoice_date > '2010-01-01' THEN 1 ELSE 0 END) AS cnt 
FROM Client AS c LEFT JOIN Invoice i ON i.client_id = c.id 
GROUP BY c.id
HAVING cnt = 0

-- IF
SELECT c.id, SUM(IF(i.invoice_date > '2010-01-01', 1, 0)) AS cnt 
FROM Client AS c LEFT JOIN Invoice i ON i.client_id = c.id 
GROUP BY c.id
HAVING cnt = 0

You can also use COUNT, but with CASE or IF:

-- CASE
SELECT client_id, COUNT(CASE WHEN invoice_date > '2010-01-01' THEN 1 ELSE NULL END) as cnt 
FROM Invoice
GROUP BY client_id HAVING cnt = 0

-- IF
SELECT client_id, COUNT(IF(invoice_date > '2010-01-01', 1, NULL)) as cnt 
FROM Invoice
GROUP BY client_id HAVING cnt = 0

demo on dbfiddle.uk

user_38408400

You can also use NOT IN with a sub-query

SELECT * FROM client 
WHERE id NOT IN (
     SELECT client_id 
     FROM invoice
     WHERE invoice_date>'2020-01-01'
);
user_117230240

Try this :-


    Select Client.* from Client Client left join Invoice Invoice on 
    client.id = Invoice.client_id and Invoice.invoice_date > "2010-01-01" 
    where Invoice.client_id is null;

Basically you include only the subset of the data after "2010-01-01" from the invoice table.

user_10079020
Related