Re: IN vs EXIIST

Quoting Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>:
>
> Jochem van Dieten wrote:
> >
> > SELECT key1, Min(CASE WHEN x THEN 1 ELSE 0 END) AS isTrue
> > FROM table
> > GROUP BY key1
> > HAVING isTrue = 1
>
> I tried this but got an error:
> psql TMP -c "select invoice_id, min(case when received then 1 else 0
> end) as ok from invoice_li group by invoice_id having ok = 1"
> ERROR: Attribute 'ok' not found

Sorry, not behind the console at the moment. Try either:

SELECT invoice_id
FROM (
SELECT invoice_id, Min(CASE WHEN received THEN 1 ELSE 0 END) AS ok
FROM invoice_li
GROUP BY invoice_id
) agg_invoices
WHERE ok = 1

or:

SELECT invoice_id, Min(CASE WHEN received THEN 1 ELSE 0 END) AS ok
FROM invoice_li
GROUP BY invoice_id
HAVING Min(CASE WHEN received THEN 1 ELSE 0 END) = 1

Make sure you have an index on invoice_id. I am not sure if it is
faster because of the case that needs to be done on each row, but I
think it is worth a shot.

Jochem