Re: sql statement: join

Thomas Steffen wrote:

> Hallo,
>
> a happy new year!
>
> I have two tables:
> table A with attribute A_ID (primary key)
> table B with attribute B_ID (primary key)
> and attribute A_ID (foreign key with A.A_ID == B.A_ID).
>
> The relation between table A and table B is 1:n; n is VARIABLES in
> dependency of A_ID.
>
> With "select A.A_ID, B.B_ID from A,B where A.A_ID == B.A_ID order by
> A.A_ID, B.B_ID asc"
> I get the following results in cursor:
> (1) A_ID1, B_ID1
> (2) A_ID1, B_ID2
> (3) A_ID2, B_ID3
> (4) A_ID2, B_ID4
> (5) A_ID2, B_ID5
> (6) A_ID3, B_ID6
> ...
>
> Now I want to get the following results in cursor:
> (1) A_ID1, num1, B_ID1, B_ID2
> (2) A_ID2, num2, B_ID3, B_ID4, B_ID5
> (3) A_ID3, num3, B_ID6
> ...
> numX is the number of of keys B_ID.
>
> How can I reslove this problem with ONE sql satement?
>
> Thank you for your hints, Thomas

If I understand what you are trying to do, and I may not, then no doubt someone can construct some complex SQL statement to do this but it is really a problem that does not lend itself to a single SQL statement as a solution. Write an anonymous block or stored procedure.

Daniel Morgan Received on Wed Jan 08 2003 - 21:02:19 CST