connecting three tables in one query

Question

I have the following tables

mixes

mid | date       | info
1   | 2009-07-01 | no info yet

music-review

mid | song              | buy
1   | Example - Example | http://example.com
2   | Exam - Exam       | http://example.com

tracklist

tid | mid | mrid
1   | 1   | 1
2   | 1   | 2

is it possible to have an SQL query where you can link these all into one?

so my results would turn out like:

date       | info        | tracklist
2009-07-01 | no info yet | Example - Example http://example.com, Exam - Exam http://example.com

or however this result would be returned... or would this need to be a two sql querys where i get the MID from the mixes and then do a query to get the tracklist from that?

Answer 1

For MySQL:

SELECT mixes.date, mixes.info, 
    GROUP_CONCAT(DISTINCT music-review.song + ' ' + music-review.buy
     ORDER BY music-review.mid ASC SEPARATOR ', ')
FROM mixes
JOIN tracklist ON tracklist.mid = mixes.mid
JOIN music-review ON music-review.mrid = tracklist.mrid
GROUP BY mixes.date, mixes.info

Answer 2

this works as adapted from mherren:

SELECT mixes.`date`, mixes.info,
CONCAT(GROUP_CONCAT(DISTINCT `music-review`.song , ' ' , `music-review`.`mid`
ORDER BY `tracklist`.`tid` ASC SEPARATOR ', ')) as `tracklist`
FROM mixes
JOIN tracklist ON tracklist.`mid` = mixes.`mid`
JOIN `music-review` ON tracklist.`mrid` = `music-review`.`mid`
WHERE `mixes`.`date`='2009-07-01'
GROUP BY mixes.`date`, mixes.info;

it fixes a blurb issue i was getting but, one thing is that group_concat has a max limit set at 1024 this can be altered tho by

SET GLOBAL group_concat_max_len=4096

Answer 3

I left so many comments, I thought it would be more helpful to suggest a revision to your architecture as an answer. However, I do think mherren has already competently addressed your actual concern, so while votes are appreciated, I don't think this should be considered as the the right "answer".

I think you need to reconsider how you have arranged the data. Specifically, you have a table for "music-review" that seems out of place while at the same time you refer to "mixes" and "tracklists" which seems a bit redundant. I imagine you want a one-to-many relationship where "mixes" refers to the information about the mix, like when it was created, the user who created it, etc. While "tracklist" is the list of songs within the "mix". What if you tried this:

#song-info
song_id | artist        | title            | online-store
1       | The Airheads  | Asthma Attack!   | example.com/?id=123
2       | The Boners    | Bite the Boner   | example.com/?id=456
3       | Cats in Heat  | Catching a Cold  | example.com/?id=789
4       | Dirty Djangos | Dig these Digits | example.com/?id=147

#mixes
mix_id | date       | info
1      | 2009-07-01 | no info yet
2      | 2009-07-02 | no info yet

#mix_tracklist
mix_id | song_id
1      | 1
1      | 2
2      | 2
2      | 3

Now you can have a list of available mixes, and if a user selects a mix, another query for the actual songs.

Trying to put all of the song data into one column should only be done if the results require that info right away or if there is a condition within the query itself that is conditional to the results of that sub-query. If you simply want to output a list of mixes with the track list for each one, you are better of doing the query for each mix based on the mix index. So in the case of php outputting HTML, you would go with:

$mixes = mysql_query("SELECT * FROM mixes WHERE date > '$last_week'");

 while($mix = mysql_fetch_assoc($mixes)) {
    $mix_id = $mix['mix_id'];
    $mix_date = date('m/d/Y', strtotime($mix['mix_id']));
    $mix_info = $mix['mix_id'];
    echo <<<EOT
    <h2 class="mix_number">$mix_number</h2>
    <p class="mix_date">$mix_date</p>
    <p class="mix_info">$mix_info</p>
    <h3>Track Listing:</h3>
           <ul class="tracklist">
    EOT;
    $tracks = mysql_query("SELECT song.artist artist, 
                                  song.title title, 
                                  song.online-store url
                                  song.song_id
                           FROM song-info song
                           JOIN mix_tracklist tracks ON (tracks.song_id = song.song_id)
                           WHERE tracks.mix_id = '$mix_id'
                           ORDER_BY song_id);
    while ($track = mysql_fetch_assoc($tracks)) {
           $artist = $track['artist'];
           $song_name = $track['title'];
           $song_url = $track['url'];
           echo <<<EOT
                 <li><a href="$song_url">$artist &ndash; $song_name</a></li>
   EOT;
   }
   echo "</ul>";
   }