Set,

Thanks for you answer, it works very fine, even adapted with my real
tables and with real datas. It is a particulary clever query :-)

Moreover, it executes very fast.

Thanks for your help.

Fabrice

--- In ib-support@y..., Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> Thanks Fabrice, I like such brain teasers!
>
> SELECT SET_ID
> FROM SET S
> WHERE NOT EXISTS (SELECT 1 FROM SET_ITEM SI
> WHERE SI.SET_ID = S.SET_ID
> AND NOT EXISTS (SELECT 1 FROM SET_ITEM SI2
> WHERE SI2.SET_ID = :S
> AND SI2.ITEM_ID = SI.ITEM_ID
> AND SI2.QUANTITY =

SI.QUANTITY)

> AND NOT EXISTS (SELECT 1 FROM SET_ITEM SI
> WHERE SI.SET_ID = :S
> AND NOT EXISTS (SELECT 1 FROM SET_ITEM SI2
> WHERE SI2.SET_ID = S.SET_ID
> AND SI2.ITEM_ID = SI.ITEM_ID
> AND SI2.QUANTITY =

SI.QUANTITY)

>
> The first exists checks that the SET_ID doesn't contain any records

that

> doesn't exist for :S, whereas the second exists should cater for

the opposite.

>
> For the supersets just use the second half of the where clause.
>
> Note that I've never tried using exists within exists, that I

haven't

> actually executed (nor prepared) the above statement, and that I do

not

> know at what speed such a query would execute.
>
> Tell me if it works - if not, I would recommend you to write a

stored

> procedure.
>
> HTH,
> Set (that's what I call myself in English, not the name of your

table)