MK15
asked on
conditional join based on column
conditional join based on column,
if table a has columns id,name,value
table b has id,name, value
i want to try joining on name first, if the value is null try joining on b
select * from a
join b on (if b.name is not null then a.name = b.name else b.value = a.value)
if table a has columns id,name,value
table b has id,name, value
i want to try joining on name first, if the value is null try joining on b
select * from a
join b on (if b.name is not null then a.name = b.name else b.value = a.value)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or maybe:
select a.*, oa1.col_in_table_b
from a
outer apply (
select top (1) *
from (
select 1 as sequence, b.*
from b
where b.name = a.name
union all
select 2, b.*
from b
where b.value = a.value
) as derived
order by sequence
) AS oa1
select a.*, oa1.col_in_table_b
from a
outer apply (
select top (1) *
from (
select 1 as sequence, b.*
from b
where b.name = a.name
union all
select 2, b.*
from b
where b.value = a.value
) as derived
order by sequence
) AS oa1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
from a
left outer join b on b.name = a.name
left outer join b b2 on b.name is null and b2.value = a.value