SQL Join query help

Question

Hi

I have 2 tables A and B with the following columns Table A - id,bId,aName,aVal Table B - id,bName

where A.bId is the same as B.id. I want a result set from a query to get

A.id, A.aName, B.bName where A.bId=B.id OR A.id, A.aName, "" when A.bId=0.

In both cases, only those records should be considered where A.aVal LIKE "aVal"

Can someone please help me with the query? I can use left join but how do I get the blank string if bId=0 and B.bName otherwise?

Thanks

Answer 1

SELECT  a.id, a.aname, COALESCE(b.bname, '')
FROM    a
LEFT JOIN
        b
ON      b.id = NULLIF(a.bld, 0)
WHERE   a.aval LIKE 'aval'