Re: JOIN question

If the maximum number of rows is fixed (like no more than 10) you can open the same table 10 times and make a
select docnum, t1.text, t2.text, t3.text etc... from main, codes t1, codes t2 etc....
where t1.docnum=main.docnum

   and t1.code <> t2.code and t1.code <> t3.code etc..

it's a bit of a typing, but it could be a solution

Eric Lansu
(From Holland)

• Original Message ----- From: "Andreas Jung" <ajung_at_sz-sb.de> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, 13 June 2000 11:10 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).
Received on Tue Jun 13 2000 - 09:42:19 CDT