SQL join multiple values from column into one cell

Question

There are a ton of sql join q's already but I didn't see my answer so here goes . . . I am working with WPDB (Wordpress database)/EZSql/MySQL 5.0. Trying to achieve the 'simple' desired output below has not proven to be easy.

Current output

MemberID          MemberName              FruitName
--------------    ---------------------   --------------
1                  Al                     Apple
1                  Al                     Cherry

Desired output

MemberID           MemberName            FruitName
-----------        --------------        ------------
1                  Al                    Apple, Cherry

MemberID comes from table a, MemberName comes from table a and table b, and FruitName comes from table b. Because I am outputting a lot of other columns from table a, I have 'left joined' the two tables through this query:

$contents = $wpdb->get_results( $wpdb->prepare("SELECT * FROM a LEFT JOIN b ON a.MemberName = b.MemberName"));

I later print the columns using echo:

        <td><?php echo $content->MemberID ?></td>
        <td><?php echo $content->MemberName ?></td>
        <td><?php echo $content->FruitName ?></td>

I assume I should try to query/join the two tables in a different manner though it may be possible to get creative in printing the columns. I found this discussion here and modeled my question after it but I don't understand their solutions and am hoping for something simpler.

Answer 1

One way is to loop and build the output in PHP (or whatever language yo are using).

Either concatenate the fruits when fetching the rows from db, or when presenting it to the user. Id suggest the latter, since it's more of a view-logic issue than anything else.

If you want to do it in SQL you'll either have to create your own (I do not belive MySQL has it built in, but I might be wrong) aggregate function to concatenate strings. It is not so trivial.

If you are fetching only one member, you can also do it in SQL by using a variable, Im uncertaing about the exact MySQL syntax, but for MSSQL it would be something like:

DECLARE @frutis VARCHAR(MAX)

SELECT @fruits = ISNULL(@fruits + ', ' + FruitName, FruitName)
FROM ... 

SELECT @fruits

Answer 2

Given that you're using mySQL, I believe the group_concat function will do exactly what you're looking for:

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

Answer 3

SELECT MemberID, MemberName, GROUP_CONCAT(FruitName SEPARATOR ',') FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY MemberID, MemberName;

Answer 4

GROUP BY MemberName and GROUP_CONCAT(FruitName). For example,

SELECT MemberId, MemberName, GROUP_CONCAT(FruitName) FROM a LEFT JOIN b ON a.MemberName = b.MemberName GROUP BY a.MemberName;