user3441151 user3441151 - 2 years ago 127
SQL Question

MySQL join three table

I have three table


userid name
1 A
2 B
3 C


trackid userid track_des
1 2 123
2 3 234
3 3 345


trackid description

I want output like this where userid=3

userid name trackid description track_des
3 C 2 BCD 234
3 C 3 NULL 345

I am using MySQL.

Answer Source

Your table1 looks to contain users and your table3 looks to contain tracks. Your table2 looks like it's a join table describing a many-to-many relationship between users and tracks. It seems the join table may contain references to tracks that don't exist in table3 eg. trackid 3. To produce the output you've shown in your example, begin by selecting the records corresponding to userid 3 and join it to table2 (your many-to-many join table). Next, you should left join to your table containing tracks. You need a left join here because some tracks listed in the join table won't be found but those rows should still be included in the output. The only task remaining is specifying the columns you would like returned. Here is what the complete query looks like:

from table1 as u
inner join table2 as ut
    on ut.userid = u.userid
left outer join table3 as t
    on t.trackid = ut.trackid
where u.userid = 3;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download