How to write custom MySQL select query for orders and their items

Question

I have done my best to describe my problem as thoroughly as I can; if you have any questions, please comment and ask. I am aware that I may have missed a very simple solution, and am interested to hear how I can make this simpler; that said, please make sure your answer considers everything I have described, and you've asked all questions (and received answers) before posting your solution.

Background

Two tables: orders and items, each containing unique information (they use IDs to connect to each other; no repeat data). See Exhibits A and B (below).

The orders table (Exhibit A) contains all information unique to that order. Each item added to the order has it's own row in the table, containing the order ID, item ID, and quantity (and other irrelevant fields).

The Problem

I am building an interface that will allow a user to filter orders by the following parameters:

• number of items (product)
• number of pieces (quantity of products)
• cost (total order cost: quantity of products multiplied by product cost)

It seems to me that collecting all data in a single MySQL query should be possible. Here's the info I need to collect:

• number of items per order
• item IDs
• quantity purchased (per product)

Example

User would like to filter all orders by the following specifications:

• three items (products)
• seven pieces (total quantity)
• less than $600

If all data (order and item) was contained in one table, the query would be something like this:

SELECT order_ID, 
       GROUP_CONCAT(item_ID) as item_IDs, 
       GROUP_CONCAT(quantity) as quantities, 
       SUM(price) as price 
FROM orders 
GROUP BY order_ID 
HAVING 7 = SUM(quantity) AND 
       3 = COUNT(*)

and return something like this:

order_ID | item_IDs | quantities | price
=========================================
15       | 22,9,36  | 1,4,2      | $582

But since the price is stored in a separate table, it is more complicated. I've created this query (see Exhibit C):

SELECT DISTINCT o.order_ID, 
       GROUP_CONCAT(o.`item_ID`) as item_IDs,
       GROUP_CONCAT(o.`quantity`) as quantities 
FROM orders o 
GROUP BY o.`order_ID` 
HAVING 7 = SUM(o.`quantity`) AND 
       3 = COUNT(*) 
ORDER BY o.`order_ID` DESC`

which gets me close, but leaves me wanting for the pricing info. I've tried using JOINS (What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?), but after hours have not been able to get anything working.

The Question

Is it possible to retrieve all of this data in one MySQL query, and/or is there a better way than the path I'm currently on (what would you do)?

It would be acceptable to for a query to return results similar to this:

order_ID | item_ID | quantity | price
======================================
177      | 42      | 2        | 50.00
177      | 45      | 3        | 30.00
177      | 46      | 2        | 10.00
150      | 39      | 3        | 25.00
150      | 47      | 1        | 95.00
150      | 41      | 3        | 15.00

so long as the query required the resulting orders to have three items and seven pieces, and total less than $600 (per the provided example).

I'm also resigned that this may not be possible within one query, and am aware of how to accomplish this using PHP; thought I might take a stab at doing at much of it in MySQL as possible, and learn something today. :)

Several of you will probably suggest changing the table structure, but I'd really rather not have tables with duplicate data, if it can be helped: if a product price is changed, I'd rather not have to update in two tables.

Thank you for your time and any solutions you may have; I am very grateful for any help you all can provide.

Exhibits/Visual Aids

Exhibit A: orders Table

ID    | order_ID | item_ID | quantity
======================================
1146  | 195      | 52      | 3
1145  | 195      | 1       | 4
1142  | 193      | 41      | 1
1141  | 193      | 40      | 3
1031  | 177      | 45      | 3
1032  | 177      | 46      | 2
1030  | 177      | 42      | 2
881   | 150      | 47      | 1
880   | 150      | 39      | 3
882   | 150      | 41      | 3

Exhibit B: items Table

(provided for completeness, not critical info) https://cldup.com/ZQEDy-vef6.png

Exhibit C: query and results

SELECT DISTINCT o.order_ID, 
       GROUP_CONCAT(o.`item_ID`) as item_IDs,
       GROUP_CONCAT(o.`quantity`) as quantities 
FROM orders o 
GROUP BY o.`order_ID` 
HAVING 7 = SUM(o.`quantity`) AND 
       3 = COUNT(*) 
ORDER BY o.`order_ID` DESC

.

order_ID | item_IDs | quantities
=================================
177      | 42,45,46 | 2,3,2
150      | 39,47,41 | 3,1,3
115      | 39,47,41 | 3,1,3
108      | 48,45,46 | 2,3,2

Exhibit D: SQLFiddle

http://sqlfiddle.com/#!9/2763f5

Answer 1

I will start here and i completed it step by step.

The first thing is that LEFT JOIN is your friend. So you can get the price of each item

Sample 1:

With this Query you get the first 100 Rows from table orders including the price of each.

SELECT o.order_ID, 
       o.item_ID as item_ID,
       o.quantity as quantitie,
       i.price
FROM orders o
LEFT JOIN items i ON o.item_ID = i.id
ORDER BY o.order_ID
LIMI 100;

Sample 2:

Now your Query must split in 2 pieces. First we will get all oder Ids that have your criteria (items,pieces and price)

     SELECT o.order_ID
        FROM orders o
        LEFT JOIN items i ON o.item_ID = i.id
        GROUP BY o.order_ID
        HAVING
         SUM(o.`quantity`) =7 
       AND
         SUM(1) = 3
       AND
         SUM(i.price) < 700;
       ORDER BY o.order_ID;

Sample 3:

Now he have all order_ids and can combine the two querys.

SELECT o.order_ID, 
       o.item_ID as item_ID,
       o.quantity as quantitie,
       i.price
FROM orders o
LEFT JOIN items i ON o.item_ID = i.id
where o.order_ID IN (
    SELECT o.order_ID
    FROM orders o
    LEFT JOIN items i ON o.item_ID = i.id
    GROUP BY o.order_ID
    HAVING
     SUM(o.`quantity`) =7 
   AND
     SUM(1) = 3
   AND
     SUM(i.price) < 700    )
ORDER BY o.order_ID, o.quantity, i.price;

But this is not testet. If you put some sample date via sqlfidle then i can direct test it for you.

Sample 4

I have change the last query to LEFT JOINS but it is not proper working on fiddle. I got a timeout. Please check it on your server.

Also i have seen that you not have good Indexes on your Tables. So the Querys can by very slow

  SELECT mo.order_ID, 
        mo.item_ID ,
        mo.quantity,
        mi.price
   FROM (
        SELECT o.order_ID
        FROM orders o
        LEFT JOIN items i ON o.item_ID = i.id
        GROUP BY o.order_ID
        HAVING
         SUM(o.`quantity`) =7 
       AND
         SUM(1) = 3
       AND
         SUM(i.price) < 700 
    ) AS ord
    LEFT JOIN orders mo ON ord.order_ID = mo.order_ID
    LEFT JOIN items  mi ON mo.item_ID = mi.id
    ORDER BY ord.order_id, mo.quantity, mi.price;

Result

+----------+---------+----------+--------+
| order_ID | item_ID | quantity | price  |
+----------+---------+----------+--------+
|      115 |      47 |        1 |  30.00 |
|      115 |      41 |        3 |  42.00 |
|      115 |      39 |        3 |  60.00 |
|      150 |      47 |        1 |  30.00 |
|      150 |      41 |        3 |  42.00 |
|      150 |      39 |        3 |  60.00 |
|      177 |      46 |        2 | 120.00 |
|      177 |      42 |        2 | 120.00 |
|      177 |      45 |        3 | 180.00 |
+----------+---------+----------+--------+
9 rows in set (0.06 sec)