ashkufaraz ashkufaraz - 4 months ago 15
SQL Question

join with different table with different condition

My Tables

Renovationinfo

BlockCode|arsehRow|SakhtemanRow
-------------------------------
1 |1 |00
2 |2 |00
3 |3 |01


ArsehMalk

BlockCode|arsehRow|name
------------------------
1 |1 |a
2 |2 |b


ApartmanMalk

BlockCode|arsehRow|SakhtemanRow|name
-------------------------------------------------
3 |3 |01 |d


i want join
Renovationinfo
with
ArsehMalk
if
SakhtemanRow=='00'


and join
Renovationinfo
with
ApartmanMalk
if
SakhtemanRow<>'00'


I write this query But this does not return any things.

Select name
From
Renovationinfo r
Left Join
ArsehMalk m1 On m1.BlockCode = r.BlockCode And
m1.arsehRow = r.arsehRow And r.SakhtemanRow = '00'
Left Join
ApartmanMalk m2 On m2.BlockCode = r.BlockCode And
m2.arsehRow = r.arsehRow And
m2.SakhtemanRow=r.SakhtemanRow And
r.SakhtemanRow<>'00'


I want get this output

BlockCode|arsehRow|name
-------------------------------
1 |1 |a
2 |2 |b
3 |3 |d

Answer

Your query has one fundamental issue: name is ambiguous. You can fix this as:

select coalesce(m1.name, m2.name) as name

Because you want additional columns:

select r.BlockCode, r.arehRow, coalesce(m1.name, m2.name) as name