CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
peter
peter

Reputation: 2526

How to join on multiple columns in sql server

I have a table called hierarchylevel and Resourcelvel the data looks like below:

HierarchyLevel

NTLogin    PreferredName   NTLevel1    NTLevel2    NTLevel3  NTLevel4   NTLevel5
Sam_peter  Peter, Sam      Geroge_Sam  Tom_Grifith James_P   Scott_Olsz Todd_L
Temuzin_P  P,Temuzin       George_Sam  Tom_Grifith James_P   Scott_Olsz Todd_L

ResourceLevel

IndusName    NTLogin     PreferredName
HCMR         James_P     P, James
TTBS         Scott_Olsz  Olsz, Scott
DAOP         George_Sam  Sam, George
BFSI         Prat_Matt   Matt, Prat

Now i want to get the Indusname where Ntlogin from Resourcelevel matches in either one of the levels in HierarchyLevel.

For Example the output should be as below form the above sample data:

IndusName        NTLogin     
 HCMR            James_P     
 TTBS            Scott_Olsz  
 DAOP            George_Sam

Upvotes: 0

Views: 58

Answers (1)

Josh Part
Josh Part

Reputation: 2164

SELECT r.IndusName, r.NTLogin
FROM ResourceLevel r
INNER JOIN HierarchyLevel h
ON r.NTLogin IN (h.NTLevel1, h.NTLevel2, h.NTLevel3, h.NTLevel4, h.NTLevel5)

or 

ON r.NTLogin = h.NTLevel1 OR r.NTLogin = h.NTLevel2 OR r.NTLogin = h.NTLevel3 OR r.NTLogin = h.NTLevel4 OR r.NTLogin = h.NTLevel5

You can use any logical condition to join two tables.

Upvotes: 3

Related Questions