I want to get some properties for each of my users. Each user can have multiple devices and be registered to multiple projects, and of course vice versa (so it's a many-to-many relationship).
I have three relevant tables for this: The table linking users to devices, the table linking users to projects, and the devices table. I have left the users table out of this for now, in order to simply the query (so I just iterate the query for each user that I get from a separate query).
What I want is to be able to display a list of devices for each user, and for each user/device pair—a list of projects. This works with the following query:
SELECT `user_devices`.*, `devices`.`id`, `devices`.`type`, `devices`.`number`, `user_projects`.`project_id` AS `projects`
FROM `user_devices`
LEFT JOIN `devices` ON `user_devices`.`device_id` = `devices`.`id`
LEFT JOIN `user_projects` ON `user_devices`.`user_id` = `user_projects`.`user_id`
AND `user_devices`.`device_id` = `user_projects`.`device_id`
WHERE `user_devices`.`user_id` = {$user_id};
However, it then includes a row for each project even with the same user/device combination, which requires post-processing (I'm using PHP), which is annoying. I therefore decided to utilize GROUP_CONCAT like so:
SELECT `user_devices`.*, `devices`.`id`, `devices`.`type`, `devices`.`number`, GROUP_CONCAT(`user_projects`.`project_id`) AS `projects`
FROM `user_devices`
LEFT JOIN `devices` ON `user_devices`.`device_id` = `devices`.`id`
LEFT JOIN `user_projects` ON `user_devices`.`user_id` = `user_projects`.`user_id`
AND `user_devices`.`device_id` = `user_projects`.`device_id`
WHERE `user_devices`.`user_id` = {$user_id};
However, what this does now is return just one row no matter what, including NULL rows (seemingly ignoring the LEFT JOIN which is supposed to only take rows that include values from the first table). It does include the list of projects correctly, but lists just one device per user even if that user has multiple devices.
I think it might be my misunderstanding of exactly how LEFT JOIN works with group functions, but I can't figure it out.
Turns out it was indeed my misunderstanding of GROUP_CONCAT. Turns out it can't work without some corresponding GROUP BY.
Since in this case logically the projects need to be shown by device, it can be grouped by devices.number:
GROUP BY `devices`.`number`