Multiple Column Sql Joins In A Table

Background: Hi there, first time posting a question so please excuse any wrongdoings... I have been given an assignment at university using ASP.NET and SQL Server, what we basicall

Solution 1:

Your problem lies in the = ROLE.ROLE_NUM. You have to specify the alias here too. Also I rearranged some numbers since your numbers were all mixed up.

SELECT MP_ROLES.MP_NUM
, MP_ROLES.ROLE_1
, MP_ROLES.ROLE_2
, MP_ROLES.ROLE_3
, MP_ROLES.ROLE_4 

FROM MP_ROLES

INNER JOIN ROLE AS ROLE_1 ON MP_ROLES.ROLE_1 = ROLE_1.ROLE_NUM
INNER JOIN ROLE AS ROLE_2 ON MP_ROLES.ROLE_2 = ROLE_2.ROLE_NUM
INNER JOIN ROLE AS ROLE_3 ON MP_ROLES.ROLE_3 = ROLE_3.ROLE_NUM
INNER JOIN ROLE AS ROLE_4 ON MP_ROLES.ROLE_4 = ROLE_4.ROLE_NUM

WHERE (MP_ROLES.MP_NUM = @MP_NUM)

Solution 2:

It looks as though what I was actually aiming for can be done using the LEFT OUTER JOIN function:

SELECT 
MP_ROLES.MP_NUM, 
MP_ROLES.ROLE_1, 
MP_ROLES.ROLE_2, 
MP_ROLES.ROLE_3, 
MP_ROLES.ROLE_4, 
ROLE1.ROLE_NUM, ROLE1.ROLE_DESCRIPTION AS POSITION1, 
ROLE2.ROLE_NUM, ROLE2.ROLE_DESCRIPTION AS POSITION2, 
ROLE3.ROLE_NUM, ROLE3.ROLE_DESCRIPTION AS POSITION3, 
ROLE4.ROLE_NUM, ROLE4.ROLE_DESCRIPTION AS POSITION4 

FROM MP_ROLES 
LEFT OUTER JOIN ROLE AS ROLE1 ON MP_ROLES.ROLE_1 = ROLE1.ROLE_NUM 
LEFT OUTER JOIN ROLE AS ROLE2 ON MP_ROLES.ROLE_2 = ROLE1.ROLE_NUM 
LEFT OUTER JOIN ROLE AS ROLE3 ON MP_ROLES.ROLE_3 = ROLE1.ROLE_NUM 
LEFT OUTER JOIN ROLE AS ROLE4 ON MP_ROLES.ROLE_4 = ROLE1.ROLE_NUM 

WHERE (MP_ROLES.MP_NUM = @MP_NUM)