using select statement as coalesce parameter

Question

I have a query like this:

select *, coalesce((select top 1 filename from logos where uid=id),'nologo.jpg') from users

but it seams that coalesce doesn't support it. how can I do this?

Answer 1

Try this one -

SELECT
      u.*
    , ISNULL(filename, 'nologo.jpg')
FROM dbo.users u
OUTER APPLY
(
    SELECT TOP 1 [filename]
    FROM dbo.logos
    WHERE uid = id
) t

Or this -

SELECT
      u.*
    , COALESCE([filename], 'nologo.jpg')
FROM dbo.users u
LEFT JOIN (
    SELECT id, [filename] = MAX([filename])
    FROM dbo.logos
    GROUP BY id
) t ON u.uid = t.id

Your query also valid -

SELECT
      *
    , COALESCE((
        SELECT TOP 1 [filename]
        FROM dbo.logos
        WHERE uid = id
      )
    , 'nologo.jpg')
FROM dbo.users