Hi @all,

is there an elegant way to find out only those values that are common to a
varying (up to 30) number of resultsets?
For example, I got 3 sets with detailnumbers and need only those which
appear in every resultset.

I tried with INTERSECT, but this obviously only works for the first 2 sets:

SELECT detailnr
FROM products
WHERE articlenr=346

INTERSECT

SELECT detailnr
FROM products
WHERE articlenr=877

INTERSECT

SELECT detailnr
FROM products
WHERE articlenr=5941

..
..
..

Thanks
Greg

Greg,

<< Is there an elegant way to find out only those values that are common
to a varying (up to 30) number of resultsets? >>

Maybe I misunderstood your requirement, but perhaps this will work?

SELECT DISTINCT detailnr
FROM products
WHERE articlenr IN (346, 877, 5941, ...., ...)

Regards,
Dan

Gregor,

<< I tried with INTERSECT, but this obviously only works for the first 2
sets: >>

If the source table is the same in all cases, then what Dan suggested should
work fine.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks for your replies, but your suggestions didn't work either.

Here is an example: I'd like to find out only those values that are common
to these resultsets

article 346: 34,44,61,122,771,966,1043,1068,1085,1141,1171,1223,1337,1378

article 877: 44,46,48,122,241,312,384,417,816,1171,1223,1265,1372,1457,1512

article 5941:44,45,58,122,397,505,537,763,807,847,1596,1141,1337,1342,1385


So only the two detailnr 122 and 44 are common to all resultsets.


The first and following INTERSECT  gave 44,122,1171,1223.

But 1171 and 1223 are not in the last recordset, so these values are not
common to all. See what I mean? Your suggestion gave 37 resultant values.

Best regards
Greg

"Gregor Dollhof" <grogg@tiscali.de> wrote in message
news:8F3038E5-5108-4F64-A660-50B840F2AD7F@news.elevatesoft.com...
> Hi @all,
>
> is there an elegant way to find out only those values that are common to a
> varying (up to 30) number of resultsets?
> For example, I got 3 sets with detailnumbers and need only those which
> appear in every resultset.
>
> I tried with INTERSECT, but this obviously only works for the first 2
> sets:

Try this (Untested, not terribly elegant)

>
> SELECT distinct detailnr
into memory\temp
> FROM products
> WHERE articlenr=346
>
UNION ALL
>
> SELECT distinct detailnr
> FROM products
> WHERE articlenr=877
>
UNION ALL
>
> SELECT distinct detailnr
> FROM products
> WHERE articlenr=5941;

SELECT detailnr,
count(*) as Mycount
from memory\temp
group by detailnr
having MyCount = 3;

Robert

Thanks Robert, it works.

In the meantime I found another approach:

SELECT detailnr
FROM products
WHERE detailnr IN

(SELECT detailnr
FROM products
WHERE detailnr IN

 (SELECT detailnr
 from products
 where products=346)

and products=877)

and products=5941

But with 30 resultsets monster-statements are needed, which I tried to avoid


Regards
Greg

of course it should read:

SELECT detailnr
FROM products
WHERE detailnr IN

(SELECT detailnr
FROM products
WHERE detailnr IN

 (SELECT detailnr
 FROM products
 WHERE articlenr=346)

AND articlenr=877)

AND articlenr=5941

Gregor,

<< Thanks for your replies, but your suggestions didn't work either. >>

Yep, I missed that the DISTINCT wouldn't resolve the membership issue for
all three.

--
Tim Young
Elevate Software
www.elevatesoft.com

Of course you could re-write all of this to be a simple inner join with
aliases.
I would think the speed would improve if files are larger and keys are
present as required.

Donat.

"Gregor Dollhof" <grogg@tiscali.de> wrote in message
news:E9A8F02E-5606-4740-9A3C-0E40C992386F@news.elevatesoft.com...
> of course it should read:
>
> SELECT detailnr
> FROM products
> WHERE detailnr IN
>
> (SELECT detailnr
> FROM products
> WHERE detailnr IN
>
>  (SELECT detailnr
>  FROM products
>  WHERE articlenr=346)
>
> AND articlenr=877)
>
> AND articlenr=5941
>
>

Donat,

<< Of course you could re-write all of this to be a simple inner join with
aliases. >>

Duh, I knew that there was a simpler way to write his query, but I could not
get it straight in my head.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com