StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POEfficient query to find singular orders for a product
text
Body
copied!<p>I have two tables orders, and line_items with following structure:</p> <pre><code>Orders (id = PK, indexes on user_id) ------------------------------------- id user_id == ====== 1 1 2 2 3 1 4 3 5 1 LineItems (id = PK, indexes on order_id and product_id) id order_id product_id quantity == ====== ======== ====== 1 1 1 1 2 1 2 2 3 2 1 4 4 2 3 6 5 3 1 1 6 4 1 1 7 5 1 1 </code></pre> <p>I am trying to find the most efficient way to solve the following requirements:</p> <ul> <li><p>Given a <code>user</code> and a <code>product</code> find the <code>LineItems</code> belonging to <code>Orders</code> where given product is the only product ordered. E.g: If user_id is 1 and product_id is 1, the query should return line items 5 and 7.</p></li> <li><p>Given a <code>user</code> and a <code>product</code> find the <code>Orders</code> where given product is the only product ordered. E.g: If user_id is 1 and product_id is 1, the query should return orders 3 and 5.</p></li> </ul> <p>The Orders and LineItems table can have millions of rows.</p> <p>I have a working solution that uses <code>COUNT</code> and <code>HAVING</code>. I am not certain that this is the most efficient solution.</p> <p>Specifically, I am wondering if this can be addressed by using the technique outlined by <code>Cletus</code> in <a href="https://stackoverflow.com/questions/477006/sql-statement-join-vs-group-by-and-having/477013#477013">this answer</a>.</p> <p><strong>Note:</strong> I am using Orders and LineItems tables to describe the scenario. My actual table is quite different and it not related to order etc. </p> <p><strong>Edit 2</strong></p> <p>Is this query efficient than using <code>GROUP BY</code> and <code>HAVING</code>?</p> <pre><code>SELECT A.id FROM LineItems A JOIN Orders B ON B.id = A.order_id AND B.user_id = 1 LEFT OUTER JOIN LineItems C ON C.order_id = A.order_id AND C.product_id != A.product_id WHERE A.product_id = 1 AND C.id IS NULL </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload