Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Unanswered: Grouping several rows data into one column

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're already using GROUP BY (good), now just add GROUP_CONCAT

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    2
    Quote Originally Posted by r937 View Post
    you're already using GROUP BY (good), now just add GROUP_CONCAT

    Yep, thought it would be that. Thanks. Added in this and it worked:

    Code:
    GROUP_CONCAT(artists.artists_name ORDER BY artists.artists_name ASC SEPARATOR ', ')

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •