CODEWITHSUNDEEP
sql
sheik JAMEEL
sheik JAMEEL

Reputation: 13

sql select statement exclude rows of same with different values

I'm using Sql Server 2008 and trying to achieve as below:

Existing Table:

+--------+-------------+--------+
| ID     | COUNTRY     | STATUS |
+--------+-------------+--------+
| 1      | UK          |   0    |
| 2      | UK          |   1    |
| 3      | USA         |   0    |
| 4      | Greece      |   0    |
| 5      | Italy       |   1    |
+-------------------------------+

I'm trying to query Where STATUS =0 and at the same time exclude all those rows if same Country has two different STATUS (Exclude UK in above table in my case)

Expected Result:

+--------+-------------+--------+
| ID     | COUNTRY     | STATUS |
+--------+-------------+--------+
| 3      | USA         |   0    |
| 4      | Greece      |   0    |
+-------------------------------+

Thanks in advance !

Upvotes: 0

Views: 763

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SELECT * 
FROM  Table1
WHERE STATUS = 0 AND
Country NOT IN (SELECT Country FROM Table1 WHERE Status = 1)

Fiddle Demo

Outpit:

+--------+-------------+--------+
| ID     | COUNTRY     | STATUS |
+--------+-------------+--------+
| 3      | USA         |   0    |
| 4      | Greece      |   0    |
+-------------------------------+

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try:

select * From tbl x 
where STATUS=0 and
    (select COUNT(*) from tbl y 
    where y.COUNTRY=x.COUNTRY and y.STATUS=1)=0

Upvotes: 1

Related Questions