Complex SQL Query Help

Question

Given a pretty standard set of related tables like Customers, Invoices and Line Items. I need to make a query like "Select all customers that have invoices that have 5 line items or more" or "Select all customers that have more than 2 invoices" or lastly "Select all customers that have line items totaling more than $100"

I'm doing this the hard way now (walking through all the records manually) and I know it's the most inefficient way but I don't know enough SQL to construct these queries. I'm using PHP5, MySQL and CakePHP 1.25.

Answer 1

For the first two you can use GROUP BY and HAVING COUNT(*)>4 and for the last one you can use SUM(field).

If you want SQL then please show your attempt.

Answer 2

Say you had:

customers.id

line_items.user_id

line_items.numItems

Query would look like:

SELECT * FROM customers
JOIN line_items
ON line_items.user_id WHERE line_items.numItems > 5

Your WHERE clause will change depending on what you wanted to return.

Haven't worked with SQL Joins in a while, but I think it's something like that.

Answer 3

Here is some help with the second one that should get you going:

   SELECT customer_id, COUNT(*) AS num_invoices
     FROM invoices
 GROUP BY customer_id 
   HAVING COUNT(*) > 2

Answer 4

Start by joining them all together:

select c.id
from customers c
left join invoices i on i.customer_id = c.id
left join lineitems li on li.invoice_id = i.id
group by c.id

To filter customers with more than 5 line items or more, add:

having count(li.id) >= 5

Filtering customers with two or more invoices is trickier, since we're joining the lineitems table. There may be multiple rows per invoice. So to count only unique invoices, we have to add distinct to the count, like:

having count(distinct i.id) >= 2

To filter customers with more than $100 in items, add:

having sum(li.cost) > 100

You can use math inside the sum, in case you're storing separate line item counts and prices:

having sum(li.itemcount * li.itemcost) > 100