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.
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')
Select * from mytable
where X not in ( Select X from mytable
Where Y <> 'F'
)
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)
GROUP BYand a condition inHAVINGclause