CODEWITHSUNDEEP
sql-server
Jiah
Jiah

Reputation: 93

Joining and selecting based on case statements

I have a 'select' code snippet which joins multiple tables together. As a prt of join condition, I want to join tables based on case statement. My pseudo as below:

case when @sample = 'type A'
then 
...
LEFT JOIN lookon lo ON lo.lookid = g.text2 AND lo.lookcat = 'Transaction'
inner join logchange logc on mc.bvin=lobg.bvin
join (
select ord=1, mcdord=1, bvin, cvbvin from @temp t where rowtype = 'ADD' union all
select ord=4, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'EDIT' union all
select ord=2, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'DEL' and rowtype not like 'EDIT' union all
select ord=9, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'CLO'
    ) A1 on A1.bvin=mc.bvin and isnull(A1.cvbvin,0) = isnull(mc.cvbvin,0)

When @sample = 'type B'
then 
...
LEFT JOIN lookon lo ON lo.lookid = g.text2 AND lo.lookcat = 'Transaction'
join ( 
    select ord=1, bvin, cvbvin from @temp t where rowtype = 'ADD' union all
    select ord=4, bvin, cvbvin from @temp t where rowtype like 'EDIT' union all
    select ord=2, bvin, cvbvin from @temp t where rowtype like 'DEL' and rowtype not like 'EDIT' 
    ) A2 on A2.bvin=mc.bvin 
END

Below is my code. I'm not getting any error but I am not getting any records pulled up for @sample = 'type B'.

  LEFT JOIN lookon lo ON lo.lookid = g.text2 AND lo.lookcat = 'Transaction'
join (
select ord=1, mcdord=1, bvin, cvbvin from @temp t where rowtype = 'ADD' union all
select ord=4, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'EDIT' union all
select ord=2, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'DEL' and rowtype not like 'EDIT' union all
select ord=9, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'CLO'
    ) A1 on A1.bvin=mc.bvin and isnull(A1.cvbvin,0) = isnull(mc.cvbvin,0) and @sample = 'type A'
join ( 
    select ord=1, bvin, cvbvin from @temp t where rowtype = 'ADD' union all
    select ord=4, bvin, cvbvin from @temp t where rowtype like 'EDIT' union all
    select ord=2, bvin, cvbvin from @temp t where rowtype like 'DEL' and rowtype not like 'EDIT' 
    ) A2 on A2.bvin=mc.bvin and @sample = 'type B'
where exists(select 1 from logchange logc where mc.bvin = logc.bvin )

Any help? Thanks!

Upvotes: 0

Views: 43

Answers (2)

Jiah
Jiah

Reputation: 93

I modified my code as below and it helped me. So the second bunch of join is pretty much the same as first one. Hence I removed it. Alsi I edited the where condition to accommodate both the sample values. Below is my code:

LEFT JOIN lookon lo ON lo.lookid = g.text2 AND lo.lookcat = 'Transaction'
    LEFT JOIN (
    select ord=1, mcdord=1, bvin, cvbvin from @temp t where rowtype = 'ADD' union all
    select ord=4, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'EDIT' union all
    select ord=2, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'DEL' and rowtype not like 'EDIT' union all
    select ord=9, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'CLO'
        )A1 on A1.bvin=mc.bvin and isnull(A1.cvbvin,0) = isnull(mc.cvbvin,0)
where (exists(select 1 from logchange logc where mc.bvin = logc.bvin )and @sample = 'typeA') or @sample = 'typeB'

Upvotes: 0

Jatin Patel
Jatin Patel

Reputation: 2104

Try changing the simple JOIN clause with LEFT JOIN.

  LEFT JOIN lookon lo ON lo.lookid = g.text2 AND lo.lookcat = 'Transaction'
LEFT JOIN (
select ord=1, mcdord=1, bvin, cvbvin from @temp t where rowtype = 'ADD' union all
select ord=4, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'EDIT' union all
select ord=2, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'DEL' and rowtype not like 'EDIT' union all
select ord=9, mcdord=1, bvin, cvbvin from @temp t where rowtype like 'CLO'
    ) A1 on A1.bvin=mc.bvin and isnull(A1.cvbvin,0) = isnull(mc.cvbvin,0) and @sample = 'type A'
LEFT JOIN ( 
    select ord=1, bvin, cvbvin from @temp t where rowtype = 'ADD' union all
    select ord=4, bvin, cvbvin from @temp t where rowtype like 'EDIT' union all
    select ord=2, bvin, cvbvin from @temp t where rowtype like 'DEL' and rowtype not like 'EDIT' 
    ) A2 on A2.bvin=mc.bvin and @sample = 'type B'
where exists(select 1 from logchange logc where mc.bvin = logc.bvin )

Upvotes: 1

Related Questions