Hi Miguel,

Conditionals in SQL are handled by the CASE statement. However, in your case, do you need to handle that in the query? Why not have an assignment after the query that assigns the values?

Also, having subqueries inside a SELECT might not be the best way to go about this. Do you really need them to be seperate columns? If not, you could do e.g. just

SELECT {StatusCore2}.[Label], COUNT(1)
FROM {DocumentCore2}
INNER JOIN {StatusCore2} ON {StatusCore2}.[Id] = {DocumentCore2}.[Status]
GROUP BY {StatusCore2}.[Label]

and then loop over the result and post-proces that. Or, if you do not even need the actual number of documents with the statusses, just a

SELECT DISTINCT {StatusCore2}.[Label]
FROM {DocumentCore2}
INNER JOIN {StatusCore2} ON {StatusCore2}.[Id] = {DocumentCore2}.[Status]

Hi Miguel,

So what exactly do you want to know then? Do you need the count, or only whether any of the types are present?

And if you set MaxRecords=1 and get [0,1] rows with the value?

SELECT TOP(1) 1

FROM documents

WHERE status=submitted

UNION ALL

SELECT TOP(1) 2

FROM documents

WHERE status=completed

UNION ALL

SELECT 3

Well, if you want it in that order, you can ORDER the output accordingly, and even if you have more than one row returned, you only need to look at the first row (so you can set the Max. Records to 1):

SELECT DISTINCT {StatusCore2}.[Label]
FROM {DocumentCore2}
INNER JOIN {StatusCore2} ON {StatusCore2}.[Id] = {DocumentCore2}.[Status]
ORDER BY
  CASE {StatusCore2}.[Label]
    WHEN 'Submitted' THEN 1
    WHEN 'Completed' THEN 2
    ELSE 3
  END

If there are any 'Submitted' documents, the first row returned will contain 'Submitted'. If there aren't any, the first row will contain 'Completed', else it will contain something else (if there are any) or the list is empty.

Nuno ninja'd me :). As for his solution, it's possible but I'd go for SELECT TOP 1 instead of SELECT DISTINCT, I think that's slightly faster. Also, use UNION ALL for the same reason.

Thanks for both answers! :D

They both work but I understand better the one from Kilian.

Even though, as Nuno's faster, that will be marked as the answer   

Answer edited as Kilian suggested. Between UNION and UNION ALL for only three one-columned rows there is no visible difference, but TOP is a lot better than DISTINCT, especially in a table for documents that keeps on growing.