StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>To return all products, even those without orders, a <code>LEFT JOIN</code> is definitely the way to go. The answer from @PP above uses "old-style" inner joins and is equivalent to this:</p> <pre><code>SELECT P.product_id ,MAX(order_timestamp) FROM Products P INNER JOIN ProductsOrdersMap M ON P.product_id = M.product_id INNER JOIN Orders O ON O.order_id = M.order_id GROUP BY P.product_id </code></pre> <p>Starting with this syntax it's a lot easier to get to the <code>LEFT JOIN</code> - just replace <code>INNER</code> with <code>LEFT</code>:</p> <pre><code>SELECT P.product_id ,MAX(order_timestamp) FROM Products P LEFT JOIN ProductsOrdersMap M ON P.product_id = M.product_id LEFT JOIN Orders O ON O.order_id = M.order_id GROUP BY P.product_id </code></pre> <hr> <p><strong>Addendum</strong>: Renato needed something more than just reworking the other answer as a <code>LEFT JOIN</code> because the <code>order_id</code> and <code>order_status</code> have to come along with the maximum timestamp. The easiest approach is to start with a list of product ID's and order ID's where the order has the maximum timestamp by <code>order_id</code>:</p> <pre><code>SELECT p2.product_id, o2.order_id FROM Products p2 INNER JOIN ProductsOrdersMap m ON p2.product_id = m.product_id INNER JOIN Orders o2 ON m.order_id = o2.order_id WHERE (o2.order_id, o2.order_timestamp) IN ( SELECT order_id, MAX(order_timestamp) FROM Orders GROUP BY order_id) </code></pre> <p>Then, instead of using <code>ProductsOrdersMap</code> to resolve products to orders, use the results from the query above:</p> <pre><code>SELECT p.product_id, o.order_id, o.TS, o.order_status FROM Products p LEFT JOIN ( SELECT p2.product_id, o2.order_id FROM Products p2 INNER JOIN ProductsOrdersMap m ON p2.product_id = m.product_id INNER JOIN Orders o2 ON m.order_id = o2.order_id WHERE (o2.order_id, o2.order_timestamp) IN ( SELECT order_id, MAX(order_timestamp) FROM Orders GROUP BY order_id) ) MaxTS ON p.product_id = MaxTS.product_id LEFT JOIN Orders o ON MaxTS.order_id = o.order_id </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