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(at)mega-bucks(dot)co(dot)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