SQL join multiple values from column into one cell

Question

There are a ton of SQL JOIN questions already, but I didn't see my answer so here it goes.

I am working with MySQL 5.0 and Wordpress Database using helper classes wpdb and ezsql. 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 the table a, MemberName comes from the tables a and b, and FruitName comes from the table b. Because I am outputting a lot of other columns from the 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.

Solution 1

Given that you're using MySQL, I believe one of the GROUP_CONCAT function (one of the available aggregate functions) will do exactly what you're looking for.

Solution 2

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