RE: JOIN question - you can't do it

From: Markellos Diorinos <mdio_at_intracom.gr>
Date: Tue, 13 Jun 2000 15:00:10 +0300
Message-Id: <10527.108889@fatcity.com>

=20
> Assume the following tables:

>=20

> MAIN(docnum number primary key,text varchar2);
> CODES(docnum number ,code varchar2);

>=20

> The CODES table contains multiple rows with the same docnum.
> I want to fetch the "text" and all "code" columns with the same docnum
> in a single row.=20

My understanding is that this is principally IMPOSSIBLE in any SQL = dialect.

You need a result set with an unknown number of columns, i.e. one row = can be 1x text, 3x code, the next one could be 1x text, 10x code. There = is no support for this in SQL.

There are a few features for 'turning arround' a table (i.e. exchanging = rows/columns) but this will no do in your case.

Anybody else knows something that I am missing?

rgds
markellos

---

Markellos J Diorinos
Intracom R&D
email : mdio_at_intracom.gr=20
tel : ++30-1-6690308 Received on Tue Jun 13 2000 - 07:00:10 CDT