Need SQL help to fetch data, with some tricky conditions

September 3, 2007 11:06 PM

From two MySQL tables, I need to fetch a list of customers who DOES NOT have an invoice issued to them within the past 2 months. More details about the table structures inside.

Here's the general structure - 2 MySQL tables, "customer" and "invoice". Table "customer" contains customer_id, customer_name. Table "invoice" has invoice_id, invoice_date, invoice_amount and customer_id.

My task - to create a report via PHP, and I need to get all customers who DOES NOT have an invoice issued to them within the last 2 months.

I can't figure out the simplest, most efficient SQL to do this.

posted by arrowhead to Computers & Internet (10 answers total)

WHERE `invoice_date` < DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
posted by Nothing at 11:10 PM on September 3, 2007

To be more specific:
SELECT * FROM `invoice`
JOIN `customer` USING(`customer_id`)
WHERE `invoice_date` < DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
posted by Nothing at 11:12 PM on September 3, 2007

Or if you need customers who have never been sent invoice to show up too, something like this:
SELECT * FROM `customer`
LEFT JOIN `invoice` USING(`customer_id`)
WHERE `invoice_date` IS NOT NULL
AND `invoice_date` < DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
posted by Nothing at 11:15 PM on September 3, 2007

To expand on Nothing's answer...

LEFT OUTER JOIN on tables (A x B) will return all records of the "left" table (A), regardless of the existence of matching records in the "right" table (B). If there is no matching row in table B, NULLs are placed instead. I think you're going to need something closer to (untested):

SELECT DISTINCT customer_id FROM customer
LEFT OUTER JOIN invoice ON customer.customer_id = invoice.customer_id
WHERE invoice.invoice_date IS NOT NULL
OR invoice.invoice_date < DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
posted by Leon at 1:25 AM on September 4, 2007

Remove the NOT from the 3rd line. Oops.
posted by Leon at 1:27 AM on September 4, 2007

I agree with Leon.
Alternatively something like this should work too:
select customer_id from customer where not exists (select invoice_id from invoice where invoice.customer_id = customer.customer_id and invoice.invoice_date < DATE_SUB(CURDATE(), INTERVAL 2 MONTH))
posted by mitocan at 4:38 AM on September 4, 2007

An inner SELECT will hammer MySQL performance.
posted by Leon at 5:25 AM on September 4, 2007

Not that it matters much, but /I think/ it will be faster for large datasets if you change

DATE_SUB(CURDATE(), INTERVAL 2 MONTH)

to

DATE_SUB('string representing now', INTERVAL 2 MONTH)

as it's obviously constant and will be optimized away. I'm just a programmer, though, not a DBA.
posted by cmiller at 5:30 AM on September 4, 2007

Unless my brain is addled from overwork, I don't think Nothing and Leon's suggestions are quite right, since they will also pick up customers who have had invoices both more than two months ago and less than two months ago. Off the top of my head, I'd try something like this (untested):

SELECT c.customer_id 
FROM customer c LEFT OUTER JOIN invoice i 
     ON c.customer_id = i.customer_id
GROUP BY c.customer_id
HAVING MAX(i.invoice_date) IS NULL
OR MAX(i.invoice_date) < date_sub(curdate(), interval 2 month)br>

It looks like miltocan's suggestion is almost right – it just has the inequality the wrong way around (probably a typo). It should be:

select customer_id from customer 
where not exists (select invoice_id from invoice 
   where invoice.customer_id = customer.customer_id 
   and invoice.invoice_date > DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) 

posted by klausness at 7:15 AM on September 8, 2007

By the way, I'm not familiar with MySQL's date functions, so I'm assuming that others have gotten that part right.

Also, I don't know how the "br>" got into my first SQL. It should be:
SELECT c.customer_id
FROM customer c LEFT OUTER JOIN invoice i
ON c.customer_id = i.customer_id
GROUP BY c.customer_id
HAVING MAX(i.invoice_date) IS NULL
OR MAX(i.invoice_date) < date_sub(curdate(), interval 2 month
posted by klausness at 7:19 AM on September 8, 2007