CODEWITHSUNDEEP
sqlsql-serverjoin
user2357446
user2357446

Reputation: 676

Use JOIN on multiple columns multiple times

I am trying to figure out the best way to use a JOIN in MSSQL in order to do the following:

I have two tables. One table contains technician IDs and an example of one data set would be as follows:

+--------+---------+---------+---------+---------+
| tagid  | techBid | techPid | techFid | techMid |
+--------+---------+---------+---------+---------+
| 1-1001 |      12 |       0 |      11 |       6 |
+--------+---------+---------+---------+---------+

I have another table that stores the names of these technicians:

+------+-----------+
| TTID | SHORTNAME |
+------+-----------+
|   11 | Steven    |
|   12 | Mark      |
|    6 | Pierce    |
+------+-----------+

If the ID of a technician in the first table is 0, there is no technician of that type for that row (types are either B, P, F, or M).

I am trying to come up with a query that will give me a result that contains all of the data from table 1 along with the shortnames from table 2 IF there is a matching ID, so the result would look something like the following:

+--------+---------+---------+---------+---------+----------------+----------------+----------------+----------------+
| tagid  | techBid | techPid | techFid | techMid | techBShortName | techPShortName | techFShortName | techMShortName |
+--------+---------+---------+---------+---------+----------------+----------------+----------------+----------------+
| 1-1001 |      12 |       0 |      11 |       6 | Mark           | NULL           | Steven         | Pierce         |
+--------+---------+---------+---------+---------+----------------+----------------+----------------+----------------+

I am trying to use a JOIN to do this, but I cannot figure out how to join on multiple columns multiple times to where it would look something like

Select table1.tagid, table1.techBid, table1.techPid, table1.techFid, table1.techMid, table2.shortname 
FROM table1 
INNER JOIN table2 on //Dont know what to put here

Upvotes: 0

Views: 46

Answers (2)

paparazzo
paparazzo

Reputation: 45096

you just do mutiple left join 

select tech.techPid, techPname.SHORTNAME 
     , tech.techFid, techFname.SHORTNAME  
from tech 
left join techName as techPname 
     on tech.techPid = techPname.TTID 
left join techName as techFname 
     on tech.techFid = techFname.TTID

Upvotes: 0

Hogan
Hogan

Reputation: 70513

You need to use left joins like this:

Select table1.tagid, table1.techBid, table1.techPid, table1.techFid, table1.techMid, 
      t2b.shortname, t2p.shortname, t2f.shortname, t2m.shortname, 
FROM table1 
LEFT JOIN table2 t2b on table1.techBid = t2b.ttid
LEFT JOIN table2 t2p on table1.techPid = t2p.ttid
LEFT JOIN table2 t2f on table1.techFid = t2f.ttid
LEFT JOIN table2 t2m on table1.techMid = t2m.ttid

Upvotes: 1

Related Questions