Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 10 total |
intersect question |
Fri, Oct 26 2007 4:17 AM | Permanent Link |
"Gregor Dollhof" | 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 |
Fri, Oct 26 2007 9:00 AM | Permanent Link |
Dan Rootham | 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 |
Fri, Oct 26 2007 3:59 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Oct 26 2007 11:28 PM | Permanent Link |
"Gregor Dollhof" | 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 |
Sat, Oct 27 2007 5:39 AM | Permanent Link |
"Robert" | "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 |
Sat, Oct 27 2007 6:49 AM | Permanent Link |
"Gregor Dollhof" | 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 |
Sat, Oct 27 2007 6:56 AM | Permanent Link |
"Gregor Dollhof" | 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 |
Mon, Oct 29 2007 3:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Oct 31 2007 12:21 PM | Permanent Link |
"Donat Hebert \(Worldsoft\)" | 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 > > |
Wed, Oct 31 2007 1:03 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Monday, June 17, 2024 at 07:11 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |