Why does this query return nothing?

Question

This query works fine, but I am having trouble trying to figure out why it doesn’t return anything if the user has no categories in the user_categories table? This is the table structure:

users: user ID, username, user city

category: 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 the new “category” column to be empty if no rows exist for user_id in user_categories ...

Thank!

Answer 1

- , . . - :

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

Wikipedia SQL JOIN - .

Answer 2

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

, MyISAM , , :

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

. :

• : GROUP BY

Answer 3

, . , .

Answer 4

, INNER JOIN user_categories. LEFT JOIN, , .