SQL Join Query Help

Question

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

where A.bId matches B.id. I want to get 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 entries where A.aVal LIKE "aVal" should be considered

Can someone please help me with a request? I can use the left join, but how to get an empty string if bId = 0 and B.bName otherwise?

thank

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'