StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You're absolutely right, it is a many-to-many query. And from what I understand, what you're looking for is the ability to have some kind of hierarchical result to display, meaning for one user, have an array of all the categories he's assigned to...</p> <p>Couple of things you could do: Option 1: Query the users table:</p> <pre><code>SELECT u.user_id, u.username, u.user_city WHERE city = 'somecity'; </code></pre> <p>From the results, get all the user_id's that match, put them in an array.</p> <pre><code>array(1,3,4,5) </code></pre> <p>Then execute a query by joining the 2 tables categories and user_categories, and passing the array as a comma separated list in a where in:</p> <pre><code>SELECT user_categories.user_id, categories.category_name FROM user_categories INNER JOIN categories ON user_categories.category_id = categories.category_id WHERE user_categories.user_id IN (1,3,4,5) </code></pre> <p>This will give you a list of user-id, category name that you can use in your script with the previous results to build your result set</p> <p>option 2: my preferred, use MySQL's GROUP_CONCAT(<a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat</a>).</p> <pre><code>SELECT users.user_id, users.user_name, GROUP_CONCAT(categories.category_name) AS categories FROM users INNER JOIN user_categories ON users.id = users_categories.user_id INNER JOIN categories ON user_categories.category_id = category.id WHERE user.user_city = 'somecity' GROUP BY user.user_id </code></pre> <p>This will return something like:</p> <pre><code>user_id username categories 1 u1 cat1, cat2, cat3 2 u2 cat1, cat3 </code></pre> <p>You can specify the separator by using SEPARATOR in group_concat.</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