SQLTeam.com | Weblogs | Forums

Help in Sql Query


#1

item | exist | option | | item | exist | option |


coke | 1 | 1 | coke | 1 | 1 |
sprit | 0 | 2 | cake| 1 | 1 |
cake | 1 | 1 | output fanta| 1 | 4 |
coke | 1 | 2 | ==========>
chips| 0 | 3 |
fanta| 1 | 4 |

I want sql query that can get all options that have full exist items, in other way option 1 is valid because all items that belong to it
are exists, and option 4 as well


#2
SELECT *
FROM   yourtable t
WHERE  NOT EXISTS
       (
           SELECT *
           FROM   yourtable x
           WHERE  x.option  = t.option
           AND    x.exist = 0
       )

#3
SELECT option
FROM table_name
GROUP BY option
HAVING MAX(CASE WHEN exist = 0 THEN 1 ELSE 0 END) = 0
--ORDER BY option --optional, of course