Let's start from remarks:
1) bad table design for
@TB2
!
2) bad SELECT statement!
Have a look at example:
DECLARE @TB1 TABLE (PK_ID1 INT IDENTITY(1,1), NAME NVARCHAR(50))
INSERT INTO @TB1 (NAME)
VALUES ('ABC'),('DEF'), ('GHI'),('JKL'), ('MNO')
DECLARE @TB2 TABLE (PK_ID2 INT IDENTITY(1,1), PROJECT NVARCHAR(10), RoleName VARCHAR(30), USRID INT)
INSERT INTO @TB2 (PROJECT , RoleName , USRID )
VALUES('proj-1', 'Manager', 1),
('proj-1', 'Leader', 4),
('proj-2', 'Manager', 3),
('proj-3', 'Manager', 3),
('proj-3', 'User', 4),
('proj-3', 'User', 1),
('proj-4', 'Manager', 2),
('proj-4', 'Leader', 1),
('proj-4', 'User', 1),
('proj-4', 'User', 1)
SELECT t2.PK_ID2, t2.PROJECT, t2.USRID, t1.NAME, t2.RoleName
FROM @TB2 AS t2 LEFT JOIN @TB1 AS t1 ON t2.USRID = t1.PK_ID1
Result:
1 proj-1 1 ABC Manager
2 proj-1 4 JKL Leader
3 proj-2 3 GHI Manager
4 proj-3 3 GHI Manager
5 proj-3 4 JKL User
6 proj-3 1 ABC User
7 proj-4 2 DEF Manager
8 proj-4 1 ABC Leader
9 proj-4 1 ABC User
10 proj-4 1 ABC User
If you want to display all roles, you need to define pivot table:
SELECT PROJECT, [Manager], [Leader], [User]
FROM (
SELECT t2.PROJECT, t1.NAME, t2.RoleName
FROM @TB2 AS t2 LEFT JOIN @TB1 AS t1 ON t2.USRID = t1.PK_ID1
) AS DT
PIVOT(MAX(Name) FOR RoleName IN([Manager], [Leader], [User])) AS PT
Result:
proj-1 ABC JKL NULL
proj-2 GHI NULL NULL
proj-3 GHI NULL JKL
proj-4 DEF ABC ABC
Do you see the difference?
Finally, i strongly recommend to read about
relational database[
^],
joins[
^], etc., like:
Visual Representation of SQL Joins[
^]
Remeber: query optimization is not possible without database optimization(structure, table definition, etc.).
[EDIT]
Please, read the comments to the Solution1.
I tried to create query using
Common Table Expressions[
^], but... (read note under below code):
;WITH Managers AS
(
SELECT t2.PK_ID2, t2.PROJECT, t2.MANAGER_ID, T1.NAME AS MANAGER_NAME, t2.LEADER_ID, t2.USER1_ID, t2.USER2_ID
FROM @TB2 AS t2 LEFT JOIN @TB1 AS t1 ON t2.MANAGER_ID = t1.PK_ID1
),
Leaders AS
(
SELECT t2.*, t1.NAME AS LEADER_NAME
FROM Managers AS t2 LEFT JOIN @TB1 AS t1 ON t2.LEADER_ID = t1.PK_ID1
),
UsersOne AS
(
SELECT t2.*, t1.NAME AS USER1_NAME
FROM Leaders AS t2 LEFT JOIN @TB1 AS t1 ON t2.USER1_ID = t1.PK_ID1
),
UsersTwo AS
(
SELECT t2.*, t1.NAME AS USER2_NAME
FROM UsersOne AS t2 LEFT JOIN @TB1 AS t1 ON t2.USER2_ID = t1.PK_ID1
)
SELECT PK_ID2, PROJECT, MANAGER_ID, MANAGER_NAME, LEADER_ID, LEADER_NAME, USER1_ID, USER1_NAME, USER2_ID, USER2_NAME
FROM UsersTwo
Note: I'm affraid about performance. I can't guarantee that this solution is faster then multi
SELECT
statement.
For further information about CTE, please see:
Using Common Table Expressions[
^]
Common Table Expressions[
^]
Recursive Queries Using Common Table Expressions[
^]
[/EDIT]