CODEWITHSUNDEEP
sqlsql-server-2008
Red Devil
Red Devil

Reputation: 2403

Need to check more than 1 condition for a column in sql server

I have a table Test:

ID Name
1 Imp
1 Critical
2 Imp
2 NA
3 NA
3 NAN

Now I want to make 3 query:

1st Need to find the ID which has Ony Imp but not critical:

Expected Output:

ID Name
2 Imp
2 NA

2nd Need to find the ID which has Imp and Critical both

Expected output:

ID Name
1 Imp
1 Critical

3rd Need to find the ID which dont have either Imp and critical both

Expected output:

3 NA
3 NAN

I tried:

  select * from test where name not in ('imp','critical')

But it give me ID 2 as well.

I tried to add IN and Not IN in my query but somehow it is not giving me correct output.

Upvotes: 0

Views: 62

Answers (1)

Max Szczurek
Max Szczurek

Reputation: 4334

You can use the EXISTS and NOT EXISTS clauses as follows:

Table variable for testing conditions:

DECLARE @test TABLE (id INT, name VARCHAR(10))
INSERT INTO @test VALUES (1, 'Imp'), (1, 'Critical'), (2, 'Imp'), (2, 'NA'), (3, 'NA'), (3, 'NAN')

Case 1 - EXISTS on one condition, and NOT EXISTS on the other.

SELECT * FROM @test t 
WHERE EXISTS (SELECT * FROM @test WHERE id = t.id AND name = 'Imp')
AND NOT EXISTS (SELECT * FROM @test WHERE id = t.id AND name = 'critical')

Returns

2   Imp
2   NA

Case 2 - EXISTS on both conditions

SELECT * FROM @test t 
WHERE EXISTS (SELECT * FROM @test WHERE id = t.id AND name = 'Imp')
AND EXISTS (SELECT * FROM @test WHERE id = t.id AND name = 'critical')

Returns

1   Imp
1   Critical

Case 3 - NOT EXISTS on both conditions

SELECT * FROM @test t 
WHERE NOT EXISTS (SELECT * FROM @test WHERE id = t.id AND name = 'Imp')
AND NOT EXISTS (SELECT * FROM @test WHERE id = t.id AND name = 'critical')

Returns

3   NA
3   NAN

Upvotes: 2

Related Questions