MySQL join multiple rows of query into one column

Question

Table structure

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.

Problem

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:

Add a new row to client_commands
For each country, I add a new row to command_countries, each referencing client_command.id
For each OS, I add a new row to command_os, each referencing client_command.id

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

Query results

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.

SELECT a.id, GROUP_CONCAT(b.country_code), GROUP_CONCAT(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 GROUP BY a.id — Mihai
– Mihai, Commented Apr 12, 2014 at 19:29

M Khalid Junaid · Accepted Answer · 2014-04-12 19:30:00Z

3

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

answered Apr 12, 2014 at 19:30

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

purpletree Over a year ago

Hey - this sort of works. But sometimes I'll have a different number of countries to operating systems per command. Your query has a result of: dl.dropboxusercontent.com/spa/3myywml11pazgf9/hz4s0rzf.png

M Khalid Junaid Over a year ago

@Prash you can use DISTINCT in GROUP_CONCAT so it will not repeat the same values

Collectives™ on Stack Overflow

MySQL join multiple rows of query into one column

Table structure

Problem

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table structure

Problem

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related