Doing a join in SQL Server

Question

I'm trying to achieve a join where the select statement has multiple column that will reference the same name in a particular table, example:

SELECT
    sh.shift_number,
    sh.workplace_num,
    wp.workplace_name,
    sh.workplace_num2,
    sh.workplace_num3
FROM shifts AS sh
INNER JOIN workplace AS wk
    ON wp.workplace_num = wk.workplace_num

My problem is I'm able to get the name of the first workplace, how do i get the same for workplace2 or workplace three

Shift_number | workplace_Num | workplace_name | workplace_Num2 | workplace_Num3
4            | 2             | Teller         | 3              | 4

As you can see o Wk_placename(Teller) displays the name of wk_placeNum(2) I'd like to be able to show the names of Wk_placeNum2 and Wk_placeNum3 they all take the workplace name from the joined workplace table!!

I'm restricted from uploading a picture, hopefully the illustration paints a picture!!

Answer 1

Try this:

SELECT
    sh.shift_number,
    sh.workplace_num,
    wk.workplace_name,
    wk2.workplace_name,
    wk3.workplace_name
FROM shifts AS sh
INNER JOIN workplace AS wk
    ON sh.workplace_num = wk.workplace_num
INNER JOIN workplace AS wk2
    ON sh.workplace_num2 = wk2.workplace_num
INNER JOIN workplace AS wk3
    ON sh.workplace_num3 = wk3.workplace_num

Answer 2

You have to join multiple times to the workplace table. Note that as you did not specified whether the fields workplace_num2 and workplace_num3 are nullable, I assumed these are so used LEFT JOIN. You should use INNER JOIN if these are not nullable:

SELECT
    sh.shift_number,
    sh.workplace_num,
    wp.workplace_name,
    sh.workplace_num2,
    wp2.workplace_name as workplace_name2,
    sh.workplace_num3
    wp3.workplace_name as workplace_name3,
FROM shifts AS sh
INNER JOIN workplace AS wp
    ON sh.workplace_num = wp.workplace_num
LEFT JOIN workplace AS wp2
    ON sh.workplace_num2 = wp2.workplace_num
LEFT JOIN workplace AS wp3
    ON sh.workplace_num3 = wp3.workplace_num