Why does this query return nothing?

Question

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!

Answer 1

SELECT  users.*, GROUP_CONCAT(categories.category_name) AS categories
FROM    users
LEFT JOIN
        user_categories ON users.user_id = user_categories.user_id
LEFT JOIN
        categories ON user_categories.category_id = categories.category_id
WHERE   users.user_city = 'brooklyn'
GROUP BY
        users.user_id
LIMIT 10

Note that on a MyISAM table, a subquery solution will probably be more efficient:

SELECT  users.*,
        (
        SELECT  GROUP_CONCAT(category_name)
        FROM    user_categories uc
        JOIN    categories c
        ON      c.category_id = uc.category_id
        WHERE   uc.user_id = users.id
        ) AS categories
FROM    users
WHERE   users.user_city = 'brooklyn'
ORDER BY
        user_id
LIMIT 10

See this article in my blog for more detail:

• Aggregates: subqueries vs. GROUP BY

Answer 2

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.

Answer 3

An inner join will not return a record if there is not a record in both tables. You may be wanting a left outer join.

Answer 4

That's because you use an INNER JOIN to user_categories. If you use a LEFT JOIN, null data will be returned for that table if there is no corresponding ID for the user.

Answer 5

Here's another great link for understanding JOINS
http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/