CODEWITHSUNDEEP
sql-serverjoin
talluri
talluri

Reputation: 11

SQL Join Multiple columns in one table with one column in another table

I got two tables as below

table 1 :Which got multiple columns which refers to a value in the second table.

tbale2:Lookup table where it got a row for every possible value for the columns in the above table

What I want to do is lookup the values in columns ItemID,ORDIG,CatID,MediaID in table 1 from ValueID in table2 and return ValueName

so at the end my result should look like

ItemID   OrgID  CatID    MediaID
i859049  Singapore  Full  0001edf

Upvotes: 1

Views: 3753

Answers (1)

Jason W
Jason W

Reputation: 13209

You will need to join to the lookup table once for each value you need, but should likely need to use a LEFT instead of INNER join since the values may be null.

SELECT 
    T1.ItemId,
    Items.ValueName AS ItemName,
    T1.OrgID,
    Orgs.ValueName AS OrgName,
    T1.CatID,
    Cats.ValueName AS CatName,
    T1.MediaID,
    Media.ValueName AS MediaName
FROM Table1 T1
    LEFT OUTER JOIN Table2 Items
        ON T1.ItemId = Items.ValueID
    LEFT OUTER JOIN Table2 Orgs
        ON T1.OrgId = Orgs.ValueID
    LEFT OUTER JOIN Table2 Cats
        ON T1.CatId = Cats.ValueID
    LEFT OUTER JOIN Table2 Media
        ON T1.MediaId = Media.ValueID

Upvotes: 2

Related Questions