CODEWITHSUNDEEP
sqlsql-server
user963070
user963070

Reputation: 639

SQL: Basic Query

I have a more detailed description ready, but I thought I'd try the simple one first.

X    Y
7    F
7    F
7    E
7    F
8    F
8    F

I want to do something else based on figuring out if for a value (x) of X there is a value of F in Y for all of the x's in the corresponding table. This means 7 doesn't cut it, and 8 does. How do I code this using a subquery? Do I use ALL? I wrote a query but it returns true whenever there is one match instead of all.

Upvotes: 0

Views: 91

Answers (3)

peterm
peterm

Reputation: 92785

You can do it without a subquery like this in most major RDBMSes 

SELECT x
  FROM table1
 GROUP BY x
HAVING COUNT(*) = SUM(CASE WHEN Y = 'F' THEN 1 ELSE 0 END)

or

SELECT x
  FROM table1
 GROUP BY x
HAVING MAX(CASE WHEN Y <> 'F' THEN 1 ELSE 0 END) = 0

Output:

| X |
|---|
| 8 |

Here is SQLFiddle demo (MySQL)
Here is SQLFiddle demo (SQL Server)
Here is SQLFiddle demo (Oracle)

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Select * from mytable where X not in ( Select X from mytable Where Y <> 'F' )

Upvotes: 1

Tilak
Tilak

Reputation: 30698

Try following query

select distinct X from temp
except
select X from temp where Y!='F' -- x,y columns, temp -> table

--Query select all distinct X which has all Y as F

Following is alternative query for the same

select distinct X from temp where not exists (select X from temp where Y='E')

Upvotes: 1

Related Questions