Retrieving alternate attribute values in GROUP BY query?

Question

Let me explain what I mean with that question:

Lets say I have to tables like these:

customers
id customer location
1   Adam     UK
2   Pete     US

values
id value
1   10
1    7
2    3
2   41

Let's ignore here for a moment that that (and the following query) wouldn't make a lot of sense. It's meant as a simplified example.

Now, if I run this query

SELECT id, customer, value FROM customers INNER JOIN values GROUP BY id

I should get this result (distinct by id)

id customer value
1    Adam    10
2    Pete     3

What I would like to be able to do is get that to use it in a search result list, but for actual displaying of the results I'd like to do something like this:

Customer: Adam
Values: 10, 7

So, basically, while I need to have a result set that's distinct for the ID, I'd still like to somehow save the rows dropped by the GROUP BY to show the values list like above. What is the best way to do this?

Answer 1

Look at http://mysql.com/group_concat - which only will work in MySql.

Better link: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat

Answer 2

Technically, the following is not valid SQL even though MySQL allows it:

Select customers.id, customers.customer, values.value 
From customers 
    Inner Join values 
        On values.id = customers.id
Group By customers.id

The SQL spec requires that every column in the Select clause be referenced in the Group By or in an aggregate function. However, given what you said later in your post, what I think you want is GROUP_CONCAT as first mentioned by Erik (+1) which is a function specific to MySQL:

Select customers.customer, Group_Concat(values.value)
From customers 
    Inner Join values 
        On values.id = customers.id
Group By customers.customer