T-SQL multiple outer join with composite key

Question

I have two source views:

View1

-----------------------------------------------------
|  UserId  |  RoleId  |  Total1 (calculated value)  |
-----------------------------------------------------
|    1     |    21    |            9                |
-----------------------------------------------------
|    1     |    22    |            5                |
-----------------------------------------------------
|    2     |    21    |            7                |
-----------------------------------------------------
|    4     |    23    |            4                |
-----------------------------------------------------

View2

-----------------------------------------------------
|  UserId  |  RoleId  |  Total2 (calculated value)  |
-----------------------------------------------------
|    1     |    21    |            2                |
-----------------------------------------------------
|    1     |    24    |            1                |
-----------------------------------------------------
|    3     |    21    |            3                |
-----------------------------------------------------
|    4     |    21    |            4                |
-----------------------------------------------------

The preferred output is based on UserId AND RoleId equality:

---------------------------------------------
|  UserId  |  RoleId  |  Total1  |  Total2  |
---------------------------------------------
|    1     |    21    |     9    |    2     |
---------------------------------------------
|    1     |    22    |     5    |   NULL   |
---------------------------------------------
|    1     |    24    |    NULL  |    1     |
---------------------------------------------
|    2     |    21    |     7    |   NULL   |
---------------------------------------------
|    3     |    21    |    NULL  |    3     |
---------------------------------------------
|   ...    |    ...   |    ...   |   ...    |

As far I know I should use some type of OUTER JOINs - but how?

score 3 · Accepted Answer · answered Jul 17 '13 at 13:23

3

SELECT
  ISNULL(v1.UserID,v2.UserID) as UserID,
  ISNULL(v1.RoleID,v2.RoleID) as RoleID,
  v1.Total1 ,
  v2.Total2
FROM view1 v1
FULL OUTER JOIN view2 v2
 ON v1.UserID = v2.UserID
 AND v1.RoleID = v2.RoleID

answered Jul 17 '13 at 13:23

EricZ

6,065
1
30
30

score 2 · Answer 2 · answered Jul 17 '13 at 13:25

Assuming there is only one matching row from each table, you would use a FULL OUTER JOIN:

SELECT COALESCE(v1.UserId, v2.UserId) AS UserId,
    COALESCE(v1.RoleId, v2.RoleId) AS RoleId,
    v1.Total1
    v2.Total2
FROM View1 v1
FULL OUTER JOIN View2 v2 ON v1.UserId = v2.UserId AND v1.RoleId = v2.RoleId

Alternatively, you can use a UNION and SUM the results, particularly useful if the rows are non-unique for each table and the join would produce duplication:

SELECT UserId, RoleId, SUM(Total1) AS Total1, SUM(Total2) AS Total2
FROM
(
    SELECT UserId, RoleId, Total1, NULL AS Total2
    FROM View1
    UNION ALL
    SELECT UserId, RoleId, NULL AS Total1, Total2
    FROM View2
) Combined
GROUP BY UserId, RoleId

+1 for the full outer join; most of the solutions I saw are union-based — OzrenTkalcecKrznaric, Jul 17 '13 at 13:29
I'm in a strange situation: both solution works perfectly. I tried EricZ's first so he'll get the point but thank you too, really. and of course +1. — boj, Jul 17 '13 at 13:39

T-SQL multiple outer join with composite key

2 Answers2