php - Complex SQL Query Help -

A beautiful standard set of related tables like customers, invoices, and line items. Like a query, "Select all customers who have 5 line items or more invoice invoices" or "Select all customers with more invoice than 2 customers" or finally "Select all customers whose line The item should be more than $ 100. " / P>

I'm doing it in a difficult way (manually running through all records) and I know that it is in the most inefficient way but I have enough SQL to build these questions. is not. I am using PHP5, MySQL and Cake PHP 1.25.

Start by adding them all together:

  Select CD to switch on Invoice i i.customer_id = c.id. Get Liittings li.invoice_id = i.id group by c.id  

Line item more than 5 or more To filter more customers, add:

  counting (li.id) & gt; = 5  

Two or more invoice customers are intriguing to filter, because we are joining the line items table, there may be several rows per invoice, then only counting unique invoices For, we have to add the different in the count, such as:

  counting (different i.id) & gt; = 2  

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

  the amount of (li.cost) & gt; 100  

You can use mathematics inside the sum if you are compiling and calculating different line items:

  being yoga (li.itemcount * li.itemcost) & gt; 100