Lakshay Jain Lakshay Jain - 3 months ago 20
SQL Question

My Sql query involving multiple tables

I have 2 tables.First table store id in multiple coloumn whose value is stored in other table.I want a query who gives result which has structure of my 1st table but values from 2nd table.To be more specific lets say i have an table like this:

Table A
Uniqueid song_1 song_2 song_3 song_4 song_5
1 2 4 5 6 8


Table B
song_id song_name
1 abcd
2 def
3 efg
4 ghi
5 abdal
6 nsadln
7 knwldn
8 jdkabdb


I want to fetch data from Table A but it should look like:

Result


Uniqueid song_1 song_2 song_3 song_4 song_5
1 def ghi abdal nsadln jdkabdb


I have used join and making objects but no luck so far.Please help me out.

Answer

Just use a bunch of left joins to get to your answer:

SELECT
    a.UniqueId
    ,s1.song_name as song_1
    ,s2.song_name as song_2
    ,s3.song_name as song_3
    ,s4.song_name as song_4
    ,s5.song_name as song_5
FROM
    TableA a
    LEFT JOIN TableB s1
    ON a.song_1 = s1.song_id 
    LEFT JOIN TableB s2
    ON a.song_2 = s2.song_id
    LEFT JOIN TableB s3
    ON a.song_3 = s3.song_id 
    LEFT JOIN TableB s4
    ON a.song_4 = s4.song_id
    LEFT JOIN TableB s5
    ON a.song_5 = s5.song_id