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

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

> >    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

> 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