title: How to write custom MySQL select query for orders and their items
body: <p>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.</p>
  
  <h1>Background</h1>
  
  <p>Two tables: <code>orders</code> and <code>items</code>, each containing unique information (they use IDs to connect to each other; no repeat data). See Exhibits A and B (below).</p>
  
  <p>The <code>orders</code> 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).</p>
  
  <h1>The Problem</h1>
  
  <p>I am building an interface that will allow a user to filter orders by the following parameters:</p>
  
  <ul>
  <li>number of items (product)</li>
  <li>number of pieces (quantity of products)</li>
  <li>cost (total order cost: quantity of products multiplied by product cost)</li>
  </ul>
  
  <p>It seems to me that collecting all data in a single MySQL query should be possible. Here's the info I need to collect:</p>
  
  <ul>
  <li>number of items per order</li>
  <li>item IDs</li>
  <li>quantity purchased (per product)</li>
  </ul>
  
  <h3>Example</h3>
  
  <p>User would like to filter all orders by the following specifications:</p>
  
  <ul>
  <li>three items (products)</li>
  <li>seven pieces (total quantity)</li>
  <li>less than $600</li>
  </ul>
  
  <p>If all data (order and item) was contained in one table, the query would be something like this:</p>
  
  <pre><code>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(*)
  </code></pre>
  
  <p>and return something like this:</p>
  
  <pre><code>order_ID | item_IDs | quantities | price
  =========================================
  15       | 22,9,36  | 1,4,2      | $582
  </code></pre>
  
  <p>But since the price is stored in a separate table, it is more complicated. I've created this query (see Exhibit C):</p>
  
  <pre><code>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`
  </code></pre>
  
  <p>which gets me close, but leaves me wanting for the pricing info. I've tried using <code>JOINS</code> (<a href="https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join">What&#39;s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?</a>), but after hours have not been able to get anything working.</p>
  
  <h2>The Question</h2>
  
  <p>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)?</p>
  
  <p>It would be acceptable to for a query to return results similar to this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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).</p>
  
  <p>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. :)</p>
  
  <p><em>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.</em></p>
  
  <p>Thank you for your time and any solutions you may have; I am very grateful for any help you all can provide.</p>
  
  <h2>Exhibits/Visual Aids</h2>
  
  <h3>Exhibit A: <code>orders</code> Table</h3>
  
  <pre><code>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
  </code></pre>
  
  <h3>Exhibit B: <code>items</code> Table</h3>
  
  <p><em>(provided for completeness, not critical info)</em>
  <a href="https://cldup.com/ZQEDy-vef6.png" rel="nofollow noreferrer">https://cldup.com/ZQEDy-vef6.png</a></p>
  
  <h3>Exhibit C: query and results</h3>
  
  <pre><code>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
  </code></pre>
  
  <p>.</p>
  
  <pre><code>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
  </code></pre>
  
  <h3>Exhibit D: SQLFiddle</h3>
  
  <p><a href="http://sqlfiddle.com/#!9/2763f5" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/2763f5</a></p>