CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
user3583912
user3583912

Reputation: 1322

multiple joins in t-sql

I got some problem in querying. I got 2 tables:

declare @t1 table(pat_ref varchar(10),pas_id varchar(10), is_mnor char(1))
declare @t2 table (prev_pat_ref varchar(10),  pat_ref varchar(10))

insert into @t1 
values ('1','111','y')
      ,('5','115','y')
      ,('6','116','y')
      ,('2','112','n')
      ,('3','113','y')
      ,('4','114','n')

insert into @t2 
values ('1','2')
      ,('5','1')
      ,('6','5')
      ,('3','4')

Now I want:

pat_ref pas_id  is_mnor major_ref
1       111     y       112
5       115     y       112
2       112     n       NULL
3       113     y       114
4       114     n       NULL

But, with my query:

 select t1.* 
 , case when t1.is_mnor='y' then t3.pas_id else null  end as major_ref
 from @t1 t1
 left join @t2 t2 on t1.pat_ref=t2.prev_pat_ref
 left join @t1 t3 on t3.pat_ref=t2.pat_ref

I am getting:

pat_ref pas_id  is_mnor major_ref
1       111     y       112
5       115     y       111 ---this should be 112
2       112     n       NULL
3       113     y       114
4       114     n       NULL

Note: initial join columns for @t1 and @t2 are t1.pat_ref=t2.prev_pat_ref

Upvotes: 1

Views: 129

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I think you want to get bottom most relations from t2 and link those rows in t1. If this is true then you will need recursive cte to get bottom most rows and then 2 joins:

;with cte as(select *, prev_pat_ref as p, 1 as l from @t2
            union all
            select t.*, c.p, c.l + 1 
            from cte c join @t2 t on t.prev_pat_ref = c.pat_ref),
bot as(select *, row_number() over(partition by p order by l desc) rn from cte)

select t1.*, case when t1.is_mnor='y' then t2.pas_id else null end as major_ref 
from @t1 t1
left join bot b on t1.pat_ref = b.p and b.rn = 1
left join @t1 t2 on b.pat_ref = t2.pat_ref

Fiddle http://sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/530

Upvotes: 3

Related Questions