I have a database with 3 tables storing event information. One stores the event details, one store the artist details and one store the artists at the events.
I am able to select all the events and venue details with one of the artists but I'd like to know how to get all the artists into one column:
The data I have is something like this:
Code:
date venue_name artists_name
2010-05-31 wembley AC/DC
2010-05-31 wembley Iron Maiden
2010-05-31 wembley Black Sabbath
This is what I have so far:
Code:
SELECT * FROM (`events`)
JOIN `venues` ON `venues`.`id` = `events`.`venue_id`
JOIN `artistsatevents` ON `artistsatevents`.`event_id` = `events`.`id`
JOIN `artists` ON `artists`.`id` = `artistsatevents`.`artist_id`
WHERE `events`.`date` > '2010-12-05 09:34:44'
GROUP BY `events`.`id`
ORDER BY `events`.`date` ASC
LIMIT 10
Which generates something like this:
Code:
id date venue_name artists_name
1 2010-05-31 wembley AC/DC
As I have grouped the records by events.id I'm only getting one result here which is what I want but without the grouping I might get 3 records returned for the same event as 3 artists are playing. What I want to do is return all artists in one column. I'm aware I might be able to use GROUP_CONCAT but can't seem to get it to work.
So ideally I want a query that returns this:
Code:
id date venue_name artists_name
1 2010-05-31 wembley AC/DC, Iron Maiden, Black Sabbath
Can anyone help?
dBforums
>
Grouping several rows data into one column
12-06-10, 05:21
Linear Mode
