CODEWITHSUNDEEP
sql-serverdatabase
Abhishek Raj
Abhishek Raj

Reputation: 490

Data x exist and data y does not for the same column of a table

I have a table named 'Student' with the following data.

enter image description here

I have to write the query which checks in the table if StudentName "RAM" exists but "SHYAM" does not exist then do something else do something else.

IF EXISTS (SELECT 1 FROM Student WHERE StudentName = 'RAM' AND StudentName <> 'SHYAM')
BEGIN
    PRINT 'DO SOMETHING'
END
ELSE
BEGIN
    PRINT 'DO SOMETHING ELSE'
END

Obviously, the above query will not work fine. But I wrote the query to understand the requirement better.

Upvotes: 0

Views: 42

Answers (2)

Jo Hacker
Jo Hacker

Reputation: 11

IF EXISTS (SELECT 1 FROM Student WHERE StudentName = 'RAM') AND NOT EXISTS (SELECT 1 FROM Student WHERE StudentName = 'SHYAM')
BEGIN
    PRINT 'DO SOMETHING'
END
ELSE
BEGIN
    PRINT 'DO SOMETHING ELSE'
END

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453910

You can filter on the two names you care about and then check the MAX (alphabetically last) is RAM and therefore there is no SHYAM record

IF EXISTS (SELECT 1
           FROM   Student
           WHERE  StudentName IN ( 'RAM', 'SHYAM' )
           HAVING MAX(StudentName) = 'RAM')
  BEGIN
      PRINT 'DO SOMETHING'
  END
ELSE
  BEGIN
      PRINT 'DO SOMETHING ELSE'
  END

If the values are dynamic and it is not known at query writing time that the one you are wanting to ensure is present is the alphabetically last one you can use

HAVING MAX(StudentName) = 'RAM'AND MIN(StudentName) = 'RAM'

Or you can use EXCEPT

IF EXISTS (SELECT 1
           FROM   Student
           WHERE  StudentName = 'RAM'
           EXCEPT
           SELECT 1
           FROM   Student
           WHERE  StudentName = 'SHYAM')
  BEGIN
      PRINT 'DO SOMETHING'
  END
ELSE
  BEGIN
      PRINT 'DO SOMETHING ELSE'
  END

Upvotes: 4

Related Questions