Q:

SQL Join query help

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

A:

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'

