7

I am building a config table and want one row to be the default value. For example, I have a list table of all my databases and a table called msg which has all my return values. In the msg table, I have one row that is 'alldbs' and I want that value returned if there isn't another row for the joined database.

So the output should be:

--LIST TABLE
dbname                                             createdate
-------------------------------------------------- -----------------------
master                                             2015-06-23 10:05:14.363
model                                              2015-06-23 10:05:14.363
msdb                                               2015-06-23 10:05:14.363
tempdb                                             2015-06-23 10:05:14.363
dummy                                              2015-06-23 10:05:14.363

--MSG Table
dbname                                             msgval
-------------------------------------------------- --------------------
alldbs                                             Message 1
dummy                                              Message 2


--DESIRED OUTPUT
dbname                                             msgval
-------------------------------------------------- --------------------
dummy                                              Message 2
master                                             Message 1
model                                              Message 1
msdb                                               Message 1
tempdb                                             Message 1

Now I can get the result, but the query is clunky and it seems like there should be a better way:

select
    a.dbname
    ,b.msgval
from
    list a
    join msg b on a.dbname = b.dbname
union all
select
    a.dbname
    ,b.msgval
from
    list a
    ,msg b
where
    a.dbname not in (select dbname from msg)
    and b.dbname = 'alldbs'

Is there a more efficient way to write this query?

SQLFiddle Link using SQL 2014.

0
8

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.

0
7

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
;
1
  • While I accepted @ypercube's as the answer, this is query has the lowest logical reads. – Mike Fal Jun 23 '15 at 17:26
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.