user3441151 user3441151 - 6 months ago 22
SQL Question

MySQL join three table

I have three table

table1

userid name
1 A
2 B
3 C


table2

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


table3

trackid description
1 ABC
2 BCD


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

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:

select
    u.*,
    ut.trackid,
    t.description,
    ut.track_des
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;
Comments