Use 'default value' in table when NULL
Using another left join
:
select
a.dbname
, coalesce(b.msgval, d.msgval) as msgval
from
list a
left join msg b on a.dbname = b.dbname
left join msg d on b.dbname is null
and d.dbname = 'alldbs' ;
or a cross
join (this requires an 'alldbs'
row to be present, otherwise it will return 0 results.):
select
a.dbname
, coalesce(b.msgval, d.msgval) as msgval
from
msg d
cross join list a
left join msg b on a.dbname = b.dbname
where d.dbname = 'alldbs' ;
SQlfiddle
Both queries assume there is only one row with dbname = 'alldbs'
. If not, use the apply
version provided by AndriyM.
Another way (not as pretty looking to me after seeing @ypercube's suggestion, but still...):
SELECT
l.dbname,
x.msgval
FROM
dbo.list AS l
CROSS APPLY
(
SELECT TOP (1)
m.msgval
FROM
dbo.msg AS m
WHERE
m.dbname IN (l.dbname, 'alldbs')
ORDER BY
CASE m.dbname WHEN 'alldbs' THEN 2 ELSE 1 END ASC
) AS x
;
Using a subselect to get the missing msg:
select a.dbname
, coalesce(b.msgval, (select msgval
from msg c
where dbname = 'alldbs')) as msgval
from list a
left join msg b
on a.dbname = b.dbname