StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Edit:</strong> <em>I initially missed the obvious requirement that we should allow for multiple items in the ready state per order. The <code>LEFT OUTER</code> join added in this edit along with a check to exclude rows where it matches anything (so only the ready item with the lowest itemID is considered) deal with this - but at this point the sub-query method is</em> far <em>clearer, and if your query planner is bright enough (which most should be these days) probably more efficient too. I've left this (corrected) answer in place for reference.</em></p> <p>Your two conditions <code>or</code>ed in the <code>where</code> clause may well cause the query planner not to use any index you may have on the <code>status</code> column. I would use the newer (SQL92) <code>JOIN</code> syntax to make it explicit that you want "things from A that are ready" as your main limiting filter like so:</p> <pre><code>SELECT assoc_with_ready.field1, assoc_with_ready.field2 FROM double_select AS ready_items LEFT OUTER JOIN double_select AS duplicate_ready_itmes ON duplicate_ready_itmes.orderID = ready_items.orderID AND duplicate_ready_itmes.status = 'ready' AND duplicate_ready_itmes.itemID < ready_items.itemID INNER JOIN double_select AS assoc_with_ready ON assoc_with_ready.orderID = ready_items.orderID WHERE ready_items.status = 'ready' AND duplicate_ready_itmes.itemID IS NULL </code></pre> <p>Note: there is no need to check for <code>status<>'ready'</code>, as the query will not return duplicate rows (some from A and some from B) in queries like this (thoughs without a <code>UNION</code> or <code>CROSS JOIN</code>), unless you want all rows associated with ready items but not the rows marked as ready themselves. If I'm reading your question wrong and you <em>don't</em> want the rows marked as ready, add <code>AND assoc_with_ready <> 'ready'</code> to the <code>ON</code> clause of the <code>JOIN</code>.</p> <p>Two points of coding style that tend to make things easier to debug later (or easier to avoid bugs in the first place): use descriptive names for table aliases (though you may have just used A and B as examples for brevity), and try to avoid selecting <code>*</code> (give explicit field names instead where possible).</p> <p>I'm not sure that your <code>GROUP BY</code> clause is trying to do, as you have not applied any aggregates at all. With your example statement I would expected errors regarding a.orderid and a.status (selected by the *) not being in the <code>group by</code> clause or an aggregate function. Even with all the selected items in aggregate functions the grouping will have no effect as ItemID is a unique column so you'll end up with one row per itemID with or without the grouping. If you just want a list of all the rows that are ready or associated with one that is ready then the query above will do that without any grouping.</p>

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