StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Edit: meh, missed the detail about selecting the username, not constraining by the username.</p> <p>Here is the correct answer:</p> <pre><code>SELECT * FROM `users_categories` AS `uc` INNER JOIN `users` AS `u` ON `u`.`ID`=`uc`.`userid` WHERE `uc`.`categoryid` IN (1,2) GROUP BY `uc`.`userid` HAVING COUNT(`uc`.`userid`) >= 2 </code></pre> <p>Fiddle: <a href="http://sqlfiddle.com/#!2/8fdca/9" rel="nofollow">http://sqlfiddle.com/#!2/8fdca/9</a></p> <p>Leaving the original answer for historical purposes.</p> <hr> <p>Yes, this is possible with table joins.</p> <p>The constraints are <code>username</code> and <code>categoryid</code> ...for <em>john</em>:</p> <pre><code>SELECT `c`.* FROM `users` AS `u` INNER JOIN `users_categories` AS `uc` ON `uc`.`userid`=`u`.`ID` AND `uc`.`categoryid` IN (1,2) INNER JOIN `categories` AS `c` ON `c`.`ID`=`uc`.`categoryid` WHERE `u`.`username`='john'; </code></pre> <p>Fiddle: <a href="http://sqlfiddle.com/#!2/8fdca/1" rel="nofollow">http://sqlfiddle.com/#!2/8fdca/1</a></p> <hr> <p>The query will also work if you remove the <code>categoryid</code> constraint (to get all categories for a given username) by removing <code>AND uc.categoryid IN (1,2)</code> ...for <em>billy</em> this time:</p> <pre><code>SELECT `c`.* FROM `users` AS `u` INNER JOIN `users_categories` AS `uc` ON `uc`.`userid`=`u`.`ID` INNER JOIN `categories` AS `c` ON `c`.`ID`=`uc`.`categoryid` WHERE `u`.`username`='billy'; </code></pre> <p>Fiddle: <a href="http://sqlfiddle.com/#!2/8fdca/3" rel="nofollow">http://sqlfiddle.com/#!2/8fdca/3</a></p> <hr> <p>Furthermore, if you already know the user's ID, this can be simplified even further ...for <em>bob</em> (id #2) now:</p> <pre><code>SELECT `c`.* FROM `users_categories` AS `uc` INNER JOIN `categories` AS `c` ON `c`.`ID`=`uc`.`categoryid` WHERE `uc`.`userid`=2; </code></pre> <p>Fiddle: <a href="http://sqlfiddle.com/#!2/8fdca/5" rel="nofollow">http://sqlfiddle.com/#!2/8fdca/5</a></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