CODEWITHSUNDEEP
sql-server
ZCoder
ZCoder

Reputation: 2339

SQL query which checks the table and returns yes or no if both values exist in the table

What I want to achieve is that using case statement or anything else I want to query the table and check if in the table we have these values then return 'yes' else 'NO.'

SELECT COUNT(Name) 
FROM [dbo].[Notification] 
WHERE Name IN ('Game', 'Gamer')

Currently this is returning the count

Upvotes: 0

Views: 1641

Answers (3)

forpas
forpas

Reputation: 164089

You can count the distinct values of Name:

SELECT CASE WHEN COUNT(DISTINCT Name) = 2 THEN 'Yes' ELSE 'No' END AS result 
FROM [dbo].[Notification] 
WHERE Name IN ('Game', 'Gamer')

Upvotes: 2

gvee
gvee

Reputation: 17161

Two options:

SELECT CASE WHEN Count(*) > 0 THEN 'Yes' ELSE 'No' END As status
FROM   dbo.Notification
WHERE  Name IN ('Game', 'Gamer')
;

Or the more faster and efficient method would be:

IF EXISTS (
  SELECT *
  FROM   dbo.Notification
  WHERE  Name IN ('Game', 'Gamer')
)
  BEGIN
    SELECT 'Yes' AS status;
  END
ELSE 
  BEGIN
    SELECT 'No' AS status;
  END
;

Upvotes: 2

Richard Hansell
Richard Hansell

Reputation: 5403

This should do the trick, it's one way, there's many other ways. Is this the best way? Probably not, but it's easy enough to follow:

SELECT
    CASE 
        WHEN EXISTS (SELECT * FROM dbo.[notification] WHERE [name] = 'Game')
            AND EXISTS (SELECT * FROM dbo.[notification] WHERE [name] = 'Gamer')
        THEN 'Yes'
        ELSE 'No'
    END;

Upvotes: 1

Related Questions