CODEWITHSUNDEEP
sql-servert-sqljoinouter-join
boj
boj

Reputation: 11395

T-SQL multiple outer join with composite key

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?

Upvotes: 0

Views: 3839

Answers (2)

lc.
lc.

Reputation: 116488

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

Upvotes: 2

EricZ
EricZ

Reputation: 6205

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

Upvotes: 3

Related Questions