开发者

SQL Subqueries COUNT CASE

I'd like a SELECT query to return specific value if the count of a nested subquery is 0 ...

SELECT
  ( SELECT (CASE COUNT(*) = 0 THEN 'TRUE' ELSE 'FALSE' END)
    FROM List
    WHERE Statu开发者_运维技巧s = 1
      AND Deleted = 1
  ) AS Status

This does not work what is wrong with this syntax?


I believe you want a case statement that looks like this:

CASE
 WHEN EXISTS(SELECT * FROM list WHERE Status = 1 AND Deleted = 1) THEN 'FALSE'
 ELSE 'TRUE'
END


Your query is missing the WHEN keyword in the CASE statement.

SELECT
  ( SELECT (CASE WHEN COUNT(*) = 0 THEN 'TRUE' ELSE 'FALSE' END)
    FROM List
    WHERE Status = 1
      AND Deleted = 1
  ) AS Status

You can go with that or you can simplify the query by removing the outer SELECT statement. And, the parentheses are optional too.

SELECT CASE WHEN COUNT(*) = 0 THEN 'TRUE' ELSE 'FALSE' END
FROM List
WHERE Status = 1
  AND Deleted = 1
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜