Re: IN vs EXIIST

You can also try:

SELECT DISTINCT( key1) FROM table EXCEPT SELECT DISTINCT( key1) from
table where not x;



Jochem van Dieten wrote:
>
> Quoting Jean-Christian Imbeault <jc@mega-bucks.co.jp>:
> >
> > The reason being that key1 is not a primary key (key1, key2 is the
> > primary key). i.e. I have a table like this
> >
> > key1  key2    x
> > ------------------
> > a     1       t
> > a     2       t
> > a     3       f
> > b     1       t
> > b     2       t
> > b     3       t
> > c     3       t
> > c     4       f
> >
> > So basically I want key1 values for which all the X's are true.
>
> SELECT     key1, Min(CASE WHEN x THEN 1 ELSE 0 END) AS isTrue
> FROM       table
> GROUP BY   key1
> HAVING     isTrue = 1
>
> > Or is my table schema wrong?
>
> I generally don't design tables with composite keys. I find it to
> complicated in many operations. But on occasion I have seen them being
> used by others very efficiently.
>
> Jochem
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster