Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
having a problem with this query |
Mon, Nov 6 2006 8:51 PM | Permanent Link |
"Lucian" | Hi,
I have 3 tables: A, B, C looking like this (with a data sample): A, fields: ARef, BRef data: ARef BRef ============== 1 1 2 2 B, fields: BRef, BName data: BRef BName ============== 1 F1 2 F2 C, fields: CRef, BRef, Val data CRef BRef Val ================== 1 1 1 2 2 1 3 2 2 Starting from table A (given ARef, say 2) I want to extract all available information from both tables B and C, taking only those rows from C that have highest Val. The result should be this: CRef BRef BName Val =========================== 1 1 F1 1 3 2 F2 2 I'l appreciate the help TIA Lucian |
Mon, Nov 6 2006 11:09 PM | Permanent Link |
"Ralf Bertoldi" | Lucian wrote:
> A, fields: ARef, BRef > data: > ARef BRef > ============== > 1 1 > 2 2 > is Table A to Table B a 1:1 Relation? I think it is... > > B, fields: BRef, BName > data: > BRef BName > ============== > 1 F1 > 2 F2 Table B to Table C should be one:many.. > C, fields: CRef, BRef, Val > data > CRef BRef Val > ================== > 1 1 1 > 2 2 1 > 3 2 2 > > Starting from table A (given ARef, say 2) > I want to extract all available information from both > tables B and C, taking only those rows from C that > have highest Val. The result should be this: > > CRef BRef BName Val > =========================== > 1 1 F1 1 > 3 2 F2 2 If you give a.ARef = 2 the result set can't be correct, or I misunderstood something... Table A (a.ARef = 2) should result in a.BRef=2 A lookup in Table B: (a.BRef=b.BRef) and there is just BRef=2 and BName=F2 If the key in Table C is BRef then BRef=1 and BName=F1 can't be there... If I'm right and it's a mistake then I would do this: /* First get all MAX values from Table C where a.ARef=2 */ SELECT max(val) maxval,bref INTO "\Memory\temp" FROM C WHERE BRef IN (SELECT BRef FROM a WHERE a.ARef=2)/*the given a.ARef=2*/ GROUP BY bref; SELECT C.CRef, A.BRef, B.BName, C.Val FROM A INNER JOIN B ON (A.BRef = B.BRef) INNER JOIN C ON (B.BRef = C.BRef) INNER JOIN "\Memory\temp" t ON (c.BRef=t.BRef) AND (c.Val=t.maxval); DROP table "\Memory\temp"; There is just one Problem: If table C would have duplicate values in "val" ..mmhh.. So first process table C... we'll try to find the highest "CRef" for the "val"... SELECT max(val) val,max(bref) bref INTO "\Memory\temp" FROM C WHERE BRef IN (SELECT BRef FROM a WHERE a.ARef=2) GROUP BY bref; /* find highest CRef */ SELECT max(c.CRef) cRef,t.bref,t.val INTO "\Memory\temp1" FROM C INNER JOIN "\Memory\temp" t ON (c.BRef=t.bref) AND (c.VAL=t.val) GROUP BY t.bref; /* do some cleanup.. */ DROP TABLE "\Memory\temp"; SELECT A.BRef, B.BName, t.CRef, t.Val FROM A INNER JOIN B ON (A.BRef = B.BRef) INNER JOIN "\Memory\temp1" t ON (a.BRef=t.BRef); DROP TABLE "\Memory\temp1"; As you can see, I like the memory tables.. maybe there is another way to get a result with a simple query.. take it as a first "thinking of.." regards Ralf |
Tue, Nov 7 2006 7:51 AM | Permanent Link |
"Lucian" | > > CRef BRef BName Val
> > =========================== > > 1 1 F1 1 > > 3 2 F2 2 > > If you give a.ARef = 2 the result set can't be correct, or I > misunderstood something... OOps, sorry, you're right. There should be only one row returned, the second one: > > 3 2 F2 2 Thanks for your input. I'll try it regards, Lucian |
Tue, Nov 7 2006 7:54 AM | Permanent Link |
"Lucian" | > There is just one Problem:
> If table C would have duplicate values in "val" ..mmhh.. No problem there are no duplicate values in "val", it would break the application. > As you can see, I like the memory tables.. Me too, I just couldn't figure it. Thanks again, Lucian |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |