MSSQL: How to SELF JOIN on two columns with OR Operator

user_7124761 0
Viewed 147

I have below tables also shared a script to generate data and related schema:

CREATE TABLE #User
(
  Id INT,
  Name NVARCHAR(200)
)

INSERT INTO #User VALUES (1, 'James')
INSERT INTO #User VALUES (2, 'Luke')
INSERT INTO #User VALUES (3, 'Miranda')
INSERT INTO #User VALUES (4, 'Lisa')

CREATE TABLE #Task
(
  Id INT,
  Name NVARCHAR(200),
  Assignee INT,
  Evaluator INT
)

INSERT INTO #Task VALUES (1, 'Test 1', 1, 3)
INSERT INTO #Task VALUES (2, 'Test 2', 0, 1)
INSERT INTO #Task VALUES (3, 'Test 3', 0, 0)
INSERT INTO #Task VALUES (4, 'Test 4', 1, 4)

What I have tried:

DECLARE @UserId INT = 1;

SELECT * 
FROM #Task T
JOIN #User U ON T.Assignee = @UserId OR T.Evaluator = @UserId

DECLARE @UserId INT = 1;
SELECT * 
FROM #Task T
JOIN #User U ON T.Assignee = @UserId AND T.Evaluator = @UserId

Expected output:

Id  Name    Assignee    Evaluator   Id    Name    EvalName 
1   Test 1     1           3         1    James    Miranda
2   Test 2     0           1         1    NULL     James
4   Test 4     1           4         1    James    Lisa

EDIT:

I have changed some data in the existing question also specified in the output, which was missed at the time of posting question.

In the updated result I just need evaluator name in case evaluator available otherwise null or empty string

sql-server sql
3 Answers

For the future, if you've got an answer that returns your expected result, but later you realise that the problem is more complicated than you originally described, you'd better ask another question and include extra details and different expected result in the new question.

In the current state of the question with expected result showing names for both Assignee and Evaluator, it could be achieved with the following query.

Get the rows we need from the Task table using filter in the WHERE. Use two left joins to lookup names from the User table twice - once for Assignees, second time for Evaluators. We need left join here, because in some rows the ID is 0.

Query

DECLARE @UserId INT = 1;
SELECT
    T.ID
    ,T.Name
    ,T.Assignee
    ,T.Evaluator
    ,@UserId AS ID
    ,Assignees.Name AS AssigneeName
    ,Evaluators.Name AS EvaluatorName
FROM
    #Task T
    LEFT JOIN #User AS Assignees ON Assignees.ID = T.Assignee
    LEFT JOIN #User AS Evaluators ON Evaluators.ID = T.Evaluator
WHERE
    T.Assignee = @UserId OR T.Evaluator = @UserId
ORDER BY
    T.ID
;

Result

+----+--------+----------+-----------+----+--------------+---------------+
| ID |  Name  | Assignee | Evaluator | ID | AssigneeName | EvaluatorName |
+----+--------+----------+-----------+----+--------------+---------------+
|  1 | Test 1 |        1 |         3 |  1 | James        | Miranda       |
|  2 | Test 2 |        0 |         1 |  1 | NULL         | James         |
|  4 | Test 4 |        1 |         4 |  1 | James        | Lisa          |
+----+--------+----------+-----------+----+--------------+---------------+
user_41160170

The variable needs to be referenced in the WHERE. Create the relationship between the 2 tables in the ON. Your attempt makes a CROSS JOIN between the Task table and any rows that have a value of 1 for either Assignee or Evaluator in the User table.

SELECT * 
FROM #Task T
     JOIN #User U ON T.Assignee = U.ID OR T.Evaluator = U.ID
WHERE U.Id = @UserId;
user_20299830 
select t.*, COALESCE(UA.Id, UE.Id) AS ID, UA.Name AS Name, UE.Name AS EvalName
FROM   #Task AS t
       LEFT OUTER JOIN #User AS UA ON t.Assignee = UA.Id
       LEFT OUTER JOIN #User AS UE ON t.Evaluator = UE.Id
WHERE  UA.Id IS NOT NULL OR UE.Id IS NOT NULL
user_126598720
Related