Q:

Complex SQL Query Help

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.

A:

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

