We help IT Professionals succeed at work.

case stmt logic

sam2929
sam2929 asked
on
HI,
I Have three columns

Table a

009 Z03 Z09
1    1       4
2
3    2       6
4    5      7

Then TABLE B
Id   KEY
1      001
2      002
5    0050
6    0060

I Want to join A with B so that if table  id don’t match table A column 009 then match against Z03 else Z09

So results should be

009     Z03    Z09     key
1            1       4        001
2                               002
3            10       6      0060 ( In this case id =Z09)
4               5      7       0050 ( In this case id =Z03)
Comment
Watch Question

Software Developer / Database Administrator
Commented:

I think this should do it ...

select a.009,
       a.Z03,
       a.Z09,
       coalesce(b1.KEY, b2.KEY, b3.KEY)
  from TableA a 
  left outer join TableB b1
    on b1.Id = a.009
  left outer join TableB b2
    on b2.Id = a.Z03
  left outer join TableB b3
    on b2.Id = a.Z09

HTH,
DaveSlash

Open in new window