StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL SELECT from array of ids where one is mandatory
text
Body
copied!<p>I'd like to know the most efficient SQL query for achieving this problem:</p> <p>Say we have a table with two columns, one storing entry ids (<code>entry_id</code>) and one storing category ids (<code>cat_id</code>):</p> <pre><code> entry_id cat_id 3 1 3 2 3 3 3 20 4 1 4 2 4 21 </code></pre> <p>I'd like to count how many distinct <code>entry_id</code>'s there are in the categories 1, 2 OR 3 but that also must be in <code>cat_id</code> 20.</p> <p>For example, categories 1, 2 and 3 might represent music genres (Country, Pop etc.), while category 20 might be recording formats (CD, Vinyl etc.). So another way of putting it verbally could be: "How many products are there that are on Vinyl and in either the Pop or Country category?"</p> <p>I could achieve this with a nested loop in code (PHP) or possibly with a nested SQL subquery, but neither feels that efficient. I feel there must be an obvious answer to this staring me in the face...</p> <p><strong>EDIT TO ADD:</strong><br> I would also like to do this without modifying the database design, as it's a third party system.</p> <p><strong>FURTHER EXAMPLE TO CLARIFY:</strong><br> Another real-world example of why I'd need this data:</p> <p>Let's say the category ids instead represent either:</p> <ul> <li>Accommodation Types (Camping = 20, Holiday Cottage = 21) </li> </ul> <p>OR</p> <ul> <li>Continents and their sub-regions (i.e. Europe = 1, UK = 2, England = 3) </li> </ul> <p>Let's say someone has selected that they are interested in camping (<code>cat_id</code> = 1). Now we need to count how many camping products there are in the Europe. A product might be tagged as both Europe (parent), UK (child) AND England (grand-child), giving us an array of category ids 1, 2 or 3. So we now need to count how many distinct products there are in both those categories AND the original accommodation category of 1 (camping).</p> <p>So having selected Camping, the end result might look something like:</p> <ul> <li>Europe: 4 camping products <ul> <li>UK: 2 camping products <ul> <li>England : 1 camping product</li> <li>Wales : 1 camping product</li> </ul></li> <li>France: 2 camping products etc.</li> </ul></li> </ul> <p>Hope that helps...</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