Why does this query return nothing?
mysql, sql
Solution
You are using the wrong type of join - inner join will only succeed if a match is found in both tables. Instead, you want to try an outer join. Try something like this:
SELECT users.*, GROUP_CONCAT(categories.category_name) AS categories
FROM users
LEFT OUTER JOIN user_categories ON users.user_id = user_categories.user_id
LEFT OUTER JOIN categories ON user_categories.category_id = categories.category_id
WHERE users.user_city = 'brooklyn'
GROUP BY users.user_id
LIMIT 10
The Wikipedia SQL JOIN article is a pretty decent summary of the join types available.
Problem
This query works fine but I'm having trouble trying to figure out why it does not return anything if the user does not have any categories in the table "user_categories"? This is the table structure: users: user id, username, user city categories: category id, category name user_categories: user id, category id ``` SELECT users.*, GROUP_CONCAT(categories.category_name) AS categories FROM users INNER JOIN user_categories ON users.user_id = user_categories.user_id INNER JOIN categories ON user_categories.category_id = categories.category_id WHERE users.user_city = 'brooklyn' GROUP BY users.user_id LIMIT 10 ``` I just need for the new column "categories" to be empty if no rows existed for the user_id in user_categories... Thanks!