Concatenate multiple fields on multiple rows with commas

Question

I have 2 tables connected by a join table. They look like the following

      servers             serverInstances          instances
| id |     ip     |     | id | sID | iID |       | id | name |
|____|____________|     |____|_____|_____|       |____|______|
| 11 | 10.0.0.100 |     |  1 |  11 |  40 |       | 40 | real |
| 12 | 10.0.0.200 |     |  2 |  11 |  41 |       | 41 | fake |
                        |  3 |  12 |  45 |       | 45 | test |

With the below query I can get the below data

SELECT s.ip, i.name
FROM servers AS s
JOIN serverInstances AS si ON s.ID = si.sID
JOIN Instances AS i ON si.iID = i.ID


|     ip     | name |
|____________|______|
| 10.0.0.100 | real |
| 10.0.0.100 | fake |
| 10.0.0.200 | test |

What I am having trouble with, is taking the above information, and writing a query that would return the following.

|     ip     | instances  |
|____________|____________|
| 10.0.0.100 | real, fake |
| 10.0.0.200 |    test    |

Is there an easy yet dynamic way to do write this query?

@Orangepill wanted to give him only a hint, not the solution. People don't always need solutions. — bwoebi
– bwoebi, Commented Jul 15, 2013 at 20:24
I was able to make the solution myself! However if you post the solution i'll give it the checkmark — mhopkins321
– mhopkins321, Commented Jul 15, 2013 at 20:25

Orangepill · Accepted Answer · 2013-07-15 20:34:11Z

3

As bwoebi stated in comments group_concat will give you this.

SELECT s.ip,  group_concat(DISTINCT i.name ORDER BY i.name ASC SEPARATOR ", " ) as instances
FROM servers AS s
JOIN serverInstances AS si ON s.ID = si.sID
JOIN Instances AS i ON si.iID = i.ID 
GROUP BY s.ip;

edited Jul 15, 2013 at 20:34

answered Jul 15, 2013 at 20:25

Orangepill

24.7k3 gold badges44 silver badges64 bronze badges

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

Comments

vitorsdcs · Accepted Answer · 2013-07-15 20:22:52Z

1

I think this will work for you

SELECT s.ip, GROUP_CONCAT(i.name)
FROM servers AS s
JOIN serverInstances AS si ON s.ID = si.sID
JOIN Instances AS i ON si.iID = i.ID
GROUP BY s.ip

answered Jul 15, 2013 at 20:22

vitorsdcs

6927 silver badges33 bronze badges

Collectives™ on Stack Overflow

Concatenate multiple fields on multiple rows with commas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related