#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171

    I need help to build this query. Probably GROUP_CONCAT


    Hello;

    I need to build a join query between 3 tables.

    The end result shows a list of upcoming events. The events are stored in sincity_events table.

    Each event may have celebrities attached to it (sincity_events_celebrities).

    Each celebrity can be either "dj" or "celebrity' (type column).

    I need to show list of events and all celebrities attending that event with this format:

    Event Name: New Years Eve.
    Celebrities: "Mike", "Luna".
    DJs: "Jenniffer", "Tiest".

    -------------------------------

    Event Name: Christmas Party
    Celebrities: "Mike", "Luna".
    DJs: none
    --------------------------------


    Here are the 3 tables:

    sincity_events_celebrities
    sincity_events
    sincity_celebrities

    -------------------------------

    CREATE TABLE IF NOT EXISTS `sincity_celebrities` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `about` text NOT NULL,
    `facebook` varchar(255) NOT NULL,
    `twitter` varchar(255) NOT NULL,
    `website` varchar(255) NOT NULL,
    `photo` varchar(255) NOT NULL,
    `type` varchar(255) NOT NULL DEFAULT 'Celebrity',
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;
    CREATE TABLE IF NOT EXISTS `sincity_events` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `recurring` varchar(255) NOT NULL,
    `title` varchar(255) NOT NULL,
    `about` text NOT NULL,
    `photo` varchar(255) NOT NULL,
    `musicians` text NOT NULL,
    `type` varchar(255) NOT NULL,
    `date` date NOT NULL,
    `display_date` varchar(255) NOT NULL,
    `price` int(11) NOT NULL,
    `recurring_day` varchar(255) NOT NULL,
    `thumbnail_photo` varchar(255) NOT NULL,
    `main_photo` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;
    CREATE TABLE IF NOT EXISTS `sincity_events_celebrities` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `event_id` int(11) NOT NULL,
    `celebrity_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=337 ;
    Thank you.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    come on, man... how long you been writing sql?

    give it a try!

    i'll give you a hint:

    you need to join the events table to two subqueries (derived tables), one for each celebrity type, and in each subquery, you'll use a GROUP_CONCAT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Originally Posted by r937
    come on, man... how long you been writing sql?

    give it a try!

    i'll give you a hint:

    you need to join the events table to two subqueries (derived tables), one for each celebrity type, and in each subquery, you'll use a GROUP_CONCAT
    Hello Rudy. Thank you for pushing me to try this. How does this look to you to start with?

    Code:
     SELECT sincity_events.title,
           sincity_events.id,
           celebs.c AS CELEBRITIES,
           djs.c    AS DJS
    FROM   sincity_events
           LEFT OUTER JOIN (SELECT GROUP_CONCAT(name) AS c,
                                   sincity_events_celebrities.event_id
                            FROM   sincity_events_celebrities
                                   INNER JOIN sincity_celebrities
                                           ON
    sincity_events_celebrities.celebrity_id =
    sincity_celebrities.id
                            WHERE  sincity_celebrities.type = 'celebrity'
                            GROUP  BY event_id) AS celebs
                        ON celebs.event_id = sincity_events.id
           LEFT OUTER JOIN (SELECT GROUP_CONCAT(name) AS c,
                                   sincity_events_celebrities.event_id
                            FROM   sincity_events_celebrities
                                   INNER JOIN sincity_celebrities
                                           ON
    sincity_events_celebrities.celebrity_id =
    sincity_celebrities.id
                            WHERE  sincity_celebrities.type = 'dj'
                            GROUP  BY event_id) AS djs
                        ON djs.event_id = sincity_events.id
    WHERE  sincity_events.date > '2013-01-26'
    ORDER  BY sincity_events.date ASC
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by zxcvbnm
    Hello Rudy. Thank you for pushing me to try this. How does this look to you to start with?
    it looks great, nice job

    how are the results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Originally Posted by r937
    it looks great, nice job

    how are the results?
    Oh thanks Rudy. The results look correct. and I am very glad I am back to learning.

    Now if I also need to grab another column within GROUP_CONCAT, how can I do that?

    For example, if I also need to get the id of each celebrity (so I can use it when I make links in the site), would it be correct to put that within GROUP_CONCAT, perhaps something like:
    Code:
    LEFT OUTER JOIN (SELECT GROUP_CONCAT(name, sincity_celebrities.id) AS c,
    ? I need a little informatoin on that please.

    An example of that is here. If you scroll down under "Upcoming Events" TODAY's event is Australia Day Long Weekend. Underneath that, there is a list of celebrities and djs that need to be linked to their profile.

    Thanks
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    i would use
    Code:
    GROUP_CONCAT(CONCAT(id,':',name))

    Comments on this post

    • English Breakfast Tea agrees : Rudy = Simply the best
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Originally Posted by r937
    i would use
    Code:
    GROUP_CONCAT(CONCAT(id,':',name))
    That looks like what I need. And when I need to use id or name seperately, I use php to break the string (using : ) and show the part of he string I need.

    Thank you
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    you got it

    btw happy oz day

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Originally Posted by r937
    you got it

    btw happy oz day

    Oh thank you
    Here on the Gold Coast we are having the strongest rain and storm.

    And also Rudy, I got a new job in Melbourne I am moving there next week. It is a large company specialized in making accounting applications.

    I need to get my query writing together before I show up. Thats why I have been back to reading your book. The good news is there are gonna be programmers who are better than me and there will be a little bit of training.

IMN logo majestic logo threadwatch logo seochat tools logo