How do I use GROUP BY to return a set of values (with MySQL)?

Vigil

This is a problem I run into often with database relations with MySQL. GROUP BY allows you to add up, average or pick the highest/lowest from all the values of a distinct field among otherwise duplicate rows. Great for doing arithmetic, but no use at all when what you want is a set of the values instead: such as a concatenated string.

Here's one specific (slightly abstracted) example:

I have a table, players, which stores the details of players attending a LAN party. It has the fields id and name.
players contains the following data:
1 John Doe
2 Jane Doe

I have another table, computers, which stores details of the computers each player has. It has the fields player_id (relates to the id field in players) and ip_address. One player may have an arbitrary number of computers.
computers contains the following data:
1 192.168.0.1
1 192.168.0.2
2 192.168.0.3

In other words, the player John Doe has brought two computers: one has the IP 192.168.0.1, and the other has the IP 192.168.0.2. Jane Doe has brought only one computer, which has the IP 192.168.0.3.

What I want to do is display a list of all the players, along with the ips of all the computers they have grouped under that player. And I want to do this in the SQL query itself with a GROUP BY.

There are GROUP BY functions that will give me the number of IP addresses John has; do arithmetic on John's IP addresses (which won't work, because it's a string); or give me the highest IP address, or the lowest. But there doesn't seem to be a function that will give all the IP addresses he has concatenated in a nice tidy string.

The workaround seems to be buffering the results and doing the grouping on the client-side (clumsy), or doing a sub-query for each player result to grab the set of computer IPs associated with them (slow for lots of results). Is there any way to do get sets of field values in a single query for an arbitrary number of relations?

tha_mink

Yeah...what you want is a join. The manual is pretty clear.

http://www.mysql.com/doc/en/JOIN.html

Vigil

No, I don't. I already use a regular left join between tables as follows:

SELECT * FROM players LEFT JOIN computers ON players.id = computers.player_id GROUP BY players.id

Perhaps I should have quoted the SELECT query being used, but I assumed that could be taken for granted. The problem is that a function to return a concatenated set (rather than a sum or average or min/max) of grouped results of a field does not seem to exist in (My)SQL, and I'm asking for a way to duplicate it without handling the grouping on the client side.

tomhath

No can do.

A query can only return individual rows, or aggregates (sum, count, etc). I don't know of any way to transform an unknown number of rows of data into that number of columns in the output. I'd just use a two way join and format the output in a while loop

If you know the upper limit on the number of computers for each player you could hard code it and use a left join. Bad design but it works:

select p.name, c1.ip_address, c2.ip_address
from players p
left join computers c1 on p.id = c1.id
left join computers c2 on p.id = c2.id
...etc

gid

select id, name, ip_address
from players p
left join computers c1 on p.id = c1.id

This will return:

id  name          ip
1   John Doe   192.168.0.1
1   John Doe   192.168.0.2
2   Jane Doe   192.168.0.3

Then in your logic just decided when the player id has changed and act accordingly.

It'll be a messy loop, but it'll work, and will only require one query. Another down side is that you're returning redudant data.