CODEWITHSUNDEEP
phpmysqlgroup-concat
mhopkins321
mhopkins321

Reputation: 3073

Multiple GROUP_CONCAT in single select statement

I have a query that is similar to the below

SELECT s.ip, 
    t.name AS 'typeName', 
    e.name AS 'envName',
    i.name,
    s.computerName
    #ap.name
    #GROUP_CONCAT(DISTINCT i.name ORDER BY i.name ASC SEPARATOR ', ' ) AS instances
    #GROUP_CONCAT(ap.name ORDER BY ap.name ASC SEPARATOR ', ' ) AS AppPool
FROM servers AS s 
JOIN types AS t ON s.typeID = t.id
JOIN environments AS e ON s.envID = e.id
JOIN serverinstances AS si ON s.id = si.serverID
JOIN instances AS i ON si.instanceID = i.id
JOIN serverapppool AS sa ON s.id = sa.serverID
JOIN apppools AS ap ON sa.appPoolID = ap.id
WHERE computerName IS NOT NULL

Results for above query

|     ip      | type | env | instance | appPool | compName |
|_____________|______|_____|__________|_________|__________|
| 192.168.1.1 |  App | test|   com1   |   fo    |   name1  |
| 192.168.1.1 |  App | test|   com1   |   bo    |   name1  |
| 192.168.1.1 |  App | test|   com2   |   fo    |   name1  |
| 192.168.1.1 |  App | test|   com2   |   bo    |   name1  |
| 192.168.1.2 |  App | test|   com1   |   fo    |   name2  |
| 192.168.1.2 |  App | test|   com1   |   bo    |   name2  |
| 192.168.1.2 |  App | test|   com2   |   fo    |   name2  |
| 192.168.1.2 |  App | test|   com2   |   bo    |   name2  |
| 192.168.1.3 |  App | prod|   com1   |   fo    |   name3  |
| 192.168.1.4 |  App | prod|   com1   |   fo    |   name4  |
| 192.168.1.5 |  App | prod|   com1   |   bo    |   name5  |

These results are accurate. However I need to condense them. The goal is to make them look like the below

|     ip      | type | env |  instances  | appPools | compName |
|_____________|______|_____|_____________|__________|__________|
| 192.168.1.1 |  App | test| com1, com2  |  fo, bo  |   name1  |
| 192.168.1.2 |  App | test| com1, com2  |  fo, bo  |   name2  |
| 192.168.1.3 |  App | prod|    com1     |    fo    |   name3  |
| 192.168.1.4 |  App | prod|    com1     |    fo    |   name4  |
| 192.168.1.5 |  App | prod|    com1     |    bo    |   name5  |

I believe this can be done through the use of GROUP_CONCAT and as you can see in the first query, I tried some various things. But can't get it to product the needed results. Thoughts?

Upvotes: 1

Views: 163

Answers (1)

Marc B
Marc B

Reputation: 360692

The GROUP in GROUP_CONCAT should tip you off - you're not actually GROUPing anything in your query, e.g. there is no GROUP BY clause. Since you haven't told MySQL how your data should be grouped, it's not doing ANY grouping. Hence only getting a single value out of your GROUP_CONCAT calls.

Most likely you'd want something more like

SELECT s.ip
etc..
etc..
GROUP BY s.ip  <---this line is missing

Upvotes: 2

Related Questions