Try this...
select a.title, group_concat(c.name SEPARATOR ' & ') as name
from album a
join albumartist b on b.albumid=a.albumid
join artist c on c.artistid=b.artistid
group by a.title;
Ed
-----Original Message-----
From: James Eaton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 15, 2006 9:11 PM
To: mysql@lists.mysql.com
Subject: Concatenate a column in multiple rows into one column?
I have a database with roughly the following structure:
album
--------------------------
albumid INT
title VARCHAR(255)
artist
--------------------------
artistid INT
name VARCHAR(255)
albumartist
--------------------------
albumid INT
artistid INT
>From the above, you can see that any given album can have more than one
artist. For instance:
album title: A Meeting by the River
artist name: Ry Cooder
artist name: V.M. Bhatt
I'd like to run a query that returns one row per album, but somehow also
returns all of the artists associated with the album (ideally
concatenated
with an ampersand seperator). Can this be done?
title artists
----------------------- --------------------------
A Meeting by the River Ry Cooder & V.M.Bhat
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
