StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Brian's <code>group_concat</code> method will work, when the number of duplicates is small, but it will fail silently when it's not. You'll never know; you'll just be missing rows that should be present.</p> <p>What you want to do is select all of the UPCs where at least one description matches (and for which duplicates exist), and then, from that list, select all rows matching each of those UPCs.</p> <p>If you group all items by UPC, then you can annotate each one with a count, and flag whether any of the descriptions matched:</p> <pre><code>SELECT upc, COUNT(*) c, MAX(`desc` LIKE '%Reed%') desc_matches FROM inventory GROUP BY upc </code></pre> <p>(This takes advantage of the fact that boolean operators, like <code>LIKE</code>, actually return <code>0</code> for false and <code>1</code> for true. Taking the maximum of that column tells you whether any row matched)</p> <p>Then you can filter that list based on your criteria, to get just the UPCs you are interested in:</p> <pre><code>SELECT upc, COUNT(*) c, MAX(`desc` LIKE '%Reed%') desc_matches FROM inventory GROUP BY upc HAVING desc_matches = 1 AND c > 1 </code></pre> <p>Once you have that list, you want to see all products that match any of those UPCs. You can do that with a simple (not OUTER) join:</p> <pre><code>SELECT a.desc, a.upc, a.sku, a.short_description FROM inventory a JOIN ( SELECT upc, COUNT(*) c, MAX(`desc` LIKE '%Reed%') desc_matches FROM inventory GROUP BY upc HAVING desc_matches = 1 AND c > 1 ) b USING (upc) </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