T-SQL Left join twice

user_1479915 0
Viewed 167

Query below works as planned, it shows exactly the way i joined it, and that is fine, but problem with it, is that if you have more "specialization" tables for users, something like "Mail type" or anything that user can have more then one data ... you would have to go two left joins for each and "give priority" via ISNULL (in this case)

I am wondering, how could I avoid using two joins and "give" priority to TypeId 2 over TypeId 1 in a single join, is that even possible?

if object_id('tempdb..#Tab1') is not null drop table #Tab1
create table #Tab1 (UserId int, TypeId int)
if object_id('tempdb..#Tab2') is not null drop table #Tab2
create table #Tab2 (TypeId int, TypeDescription nvarchar(50))

insert into #Tab1 (UserId, TypeId)
values
(1, 1),
(1, 2)
insert into #Tab2 (TypeId, TypeDescription)
values
(1, 'User'),
(2, 'Admin')

select *, ISNULL(t2.TypeDescription, t3.TypeDescription) [Role]
from #Tab1 t1
    LEFT JOIN #Tab2 t2 on t1.TypeId = t2.TypeId and
                          t2.TypeId = 2
    LEFT JOIN #Tab2 t3 on t1.TypeId = t3.TypeId and
                          t3.TypeId = 1
left-join tsql sql-server
2 Answers

The first problem is determining priority. In this case, you could use the largest TypeId, but that does not seem like a great idea. You could add another column to serve as a priority ordinal instead.

From there, it is a top 1 per group query:

using top with ties and row_number():

select top 1 with ties
    t1.UserId, t1.TypeId, t2.TypeDescription
from #Tab1 t1
  left join #Tab2 t2
    on t1.TypeId = t2.TypeId
order by row_number() over (
      partition by t1.UserId 
      order by t2.Ordinal
      --order by t1.TypeId desc
)

using common table expression and row_number():

;with cte as (
select t1.UserId, t1.TypeId, t2.TypeDescription
  , rn = row_number() over (
      partition by t1.UserId 
      order by t2.Ordinal
      --order by t1.TypeId desc
    )
from #Tab1 t1
  left join #Tab2 t2
    on t1.TypeId = t2.TypeId
)
select UserId, TypeId, TypeDescription
from cte
where rn = 1

rextester demo for both: http://rextester.com/KQAV36173

both return:

+--------+--------+-----------------+
| UserId | TypeId | TypeDescription |
+--------+--------+-----------------+
|      1 |      2 | Admin           |
+--------+--------+-----------------+
user_23334990

Actually I don't think you don't need a join at all. But you have to take the max TypeID without respect to the TypeDescription, since these differences can defeat a Group By. So a workaround is to take the Max without TypeDescription initially, then subquery the result to get the TypeDescription.

SELECT dT.*
      ,(SELECT TypeDescription FROM #Tab2 T2 WHERE T2.TypeId = dT.TypeId) [Role] --2. Subqueries TypeDescription using the Max TypeID
  FROM (
         select t1.UserId
               ,MAX(T1.TypeId) [TypeId] 
                --, T1.TypeDescription AS [Role] --1. differences will defeat group by. Subquery for value later in receiving query.
          from #Tab1 t1                
         GROUP BY t1.UserId
       ) AS dT

Produces Output:

UserId  TypeId  Role
1       2       Admin
user_30643450
Related