Reputation: 1943
client_commands (the "main" table):
id | completed
command_countries:
id | command_id | country_code
command_os:
id | command_id |OS
command_id on references the id column on client_commands.
I can add client commands with filters based on countries and operating systems. To try and normalise my DB structure, for each new command added:
For one of the pages on my site, I need to display all client_commands (where completed = 0) as well as all the countries and operating systems for that command. My desired output would be something like:
id | countries | OS
1 | GB, US, FR| 2, 3
2 | ES, HU | 1, 3
I'm not sure how to go about doing this. The current query I'm using returns multiple rows:
SELECT a.id, b.country_code, c.OS
FROM client_commands a
LEFT JOIN command_countries b on b.command_id = a.id
LEFT JOIN command_os c on c.command_id = a.id
WHERE a.completed = 0
Any help?
Thanks!
EDIT: I forgot to mention (if you couldn't infer from above) - there can be a different number of operating systems and countries per command.
--
Also: I know I could do this by pulling all the commands, then looping through and running 2 additional queries for each result. But if I can, I'd like to do it as efficiently as possible with one query.
Upvotes: 2
Views: 1101
Reputation: 64496
You can do this in one query by using GROUP_CONCAT
SELECT a.id,
GROUP_CONCAT(DISTINCT b.country_code SEPARATOR ' ,') `countries`,
GROUP_CONCAT(DISTINCT c.OS SEPARATOR ' ,') `os`,
FROM client_commands a
LEFT JOIN command_countries b on b.command_id = a.id
LEFT JOIN command_os c on c.command_id = a.id
WHERE a.completed = 0
GROUP BY a.id
if you want the ordered results in in a row you can use ORDER BY in GROUP_CONCAT
like
GROUP_CONCAT(b.country_code ORDER BY b.command_id DESC SEPARATOR ' ,') `countries`
But be aware of that fact it has a limit of 1024 character to concat set by default but this can be increased b,steps provided in manual
Upvotes: 3