Multi joined MySQL query has an issue -

i have following query, job scenario is:

i have 3 tables. categories, roles , roles_to_categories table.

roles_to_categories table:

--id--   --role_id-- --category_id-- 

a role in roles table, may bound several categories in pivot table named roles_to_categories. want role, instance admin fetch categories table categories bound in table roles_to_categories.

it works fine, problem when aggregation comes in: group_concat(). want list of parent categories, , include children in separate column using group_concat(). problem group_concat() seems ignore roles_to_categories pivot table when retrieves list of children, means includes children of category not allowed (i mean there no record in roles_to_categories bind role)

the query is:

select categories.name cat_name, categories.id cat_id,  roles.name role_name, roles.id role_id, roles_to_categories.id cat_id, roles_to_categories.role_id role_id, roles_to_categories.category_id, group_concat('name: ', categories2.name) cat_children roles left join roles_to_categories on roles.id = roles_to_categories.role_id left join categories on roles_to_categories.category_id = categories.id  left join categories categories2 on categories2.parent_id = categories.id roles.name = "admin"; 

it selects required columns 3 tables, starting roles left joins roles_to_categories related records , re-left-joins on categories table roles_to_categories select categories ids exists in fetched records , ....

group_concat aggregate - you'll need group non-aggregated columns in select list.

you've included multiple aliases of both sides of joined columns in query - confusing , redundant, , aliased category.id , roles_to_category.id cat_id.

in case, since want concatenate children of parent category, so:

select    categories.name cat_name, categories.id cat_id,    roles.name role_name, roles.id role_id,   roles_to_categories.id roles_to_cat_id,   group_concat('name: ', categories2.name) cat_children    roles left join roles_to_categories     on roles.id = roles_to_categories.role_id    left join categories on roles_to_categories.category_id = categories.id     left join categories categories2 on categories2.parent_id = categories.id    roles.name = 'admin' group    categories.name, categories.id, roles.name, roles.id, roles_to_categories.id;