RE: JOIN question

My first answer was, You Can't. But then I thought, you could make a cursor on the CODES table.
Each time you fetch a new row from the codes table, you can concatenate the code number to some variable with a space or something to separate, then when there are no more rows, you can select the text from the main table where the docnum is the one you have in the cursor..

Hope this helps,
Debbie
ACP -----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Andreas Jung
Sent: Tuesday, June 13, 2000 5:10 AM
To: Multiple recipients of list ORACLE-L Subject: JOIN question

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.

I tried:
select main.docnum,main.text,codes.code where main.docnum=codes.docnum

But this gives me a row for every row in CODES but not a single one. How can I reduce the multiple rows to a single one ?

Andreas

--
Author: Andreas Jung
  INET: ajung_at_sz-sb.de

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

_____________________________________________
NetZero - Defenders of the Free World
Click here for FREE Internet Access and Email