Join Relational tables with 'GROUP_CONCAT"?

[justlikethat]

Hello, thanks to a lot of resources and some helpful people along the way, I was able to create a relational database for the purpose of showing events - however I am stuck now.

 

My XML query searches for events.

 

The events table has a foreign key linking to the venues table, which links to the venue_types and locations tables. Some very simple one-to-many relationship I did.

 

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

What my problem is now, is that each event also has multiple music genres. Because of that, I created two additional tables.

 

music_styles and events_music_styles

 

The music_styles table contains an ID and the corresponding name of that musical style attached to that ID.

 

The events_music_styles table contains the ID of the events table and the music_styles table.

 

This way, if an event has two musical genres, the table in events_music_styles would look something like this:

 

EVENT_ID MUSIC_STYLE_ID

1 2

1 4

 

That would say that event 1 has music styles 2 and 4.

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

 

 

What I want to do is link this events_music_styles table to the events table, and use GROUP_CONCAT to combine the two rows returned by events into one.

 

So if it looks like this:

 

Event_id event_name music_style_id

1 event 1 2

1 event 1 4

 

I want it to turn into this:

 

Event_id event_name music_style_name

1 event 1 latino, reggae

 

 

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

I made a small database mockup and this is what it looks like:

 

 

 

Right now the events_music_styles table isn't linked to my events table so the xml result looks like this:

 

 

And the code behind it looks like this:

Code:

 

$query = 'SELECT events.*, venues.*, locations.*'.
       'FROM events '.
       'INNER JOIN venues ON(events.VENUE_LOCATION = venues.ID)'.
       'INNER JOIN locations ON(events.VENUE_LOCATION = locations.ID)';

 

 

 

Code:

 

  echo '<marker ';
  echo 'id="' . parseToXML($row['ID']) . '" ';
  echo 'date="' . $row['EVENT_DATE'] . '" ';
  echo 'event_name="' . $row['EVENT_NAME'] . '" ';
  echo 'location="' . $row['LOCATION'] . '" ';
  echo 'place="' . $row['VENUE_NAME'] . '" ';
  echo '/>';

 

 

 

 

I'm doing tons of reading on this subject so hopefully I won't be too much clueless if someone tries to give me any tips. Thanks everyone and sorry for such a long post Thanks for your time as well ! )))

[fenway]

Why are you using GROUP_CONCAT?