RE: JOIN question - you can't do it

From: Naresh Jain <njain_at_amcomsoft.com>
Date: Tue, 13 Jun 2000 14:06:50 -0500
Message-Id: <10527.109049@fatcity.com>

I reckon it can be done, although it is ugly. 1. Find the max number of codes there are for a docnum in the tables. maxcount <=> select max(count(code)) from codes group by docnum; 2. then do a join between main and codes on docnum maxcount times. this will get you all the codes in a single row.
Its ugliness is inversely proportional to the desperation of your need! NJ

-----Original Message-----
From: Glenn Travis [mailto:Glenn.Travis_at_wcom.com] Sent: Tuesday, June 13, 2000 2:21 PM
To: Multiple recipients of list ORACLE-L Subject: RE: JOIN question - you can't do it

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Markellos
> Diorinos
> Sent: Tuesday, June 13, 2000 9:08 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: JOIN question - you can't do it
>
>
>
> > Assume the following tables:
> >
> > MAIN(docnum number primary key,text varchar2);
> > CODES(docnum number ,code varchar2);
> >
> > 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.
>
> 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.
>

Can you explain these features in more detail?

Given table A:

col1        col2
----        ----
Manager     John

Executive Mark
Customer James
President Tom

Provide a single query (or a procedure) which returns;

Manager     Executive     Customer     President
-------     ---------     --------     ---------
John        Mark          James        Tom

What happens when you have table A: ?

col1        col2
----        ----
Manager     John

Executive Mark
Customer James
President Tom
Customer Jim

This is a simple example. A more realistic example would have multiple tables and col1 would be the code value describing the 'position' and a joinback needed to get the columns headings (Manager, ...) in the output row.

I've seen this asked before but cannot locate the solutions. any ideas?

---

Glenn Travis
MCI WorldCom
Enterprise Systems Development - DBA
Glenn.Travis_at_wcom.com

> Anybody else knows something that I am missing?
>
>
> rgds
> markellos
>
> ----
> Markellos J Diorinos
> Intracom R&D
> email : mdio_at_intracom.gr
> tel : ++30-1-6690308
> fax : ++30-1-6860312
>
> --
> Author: Markellos Diorinos
> INET: mdio_at_intracom.gr
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Author: Glenn Travis
  INET: Glenn.Travis_at_wcom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Tue Jun 13 2000 - 14:06:50 CDT