Re: Strange Join needed...

From: Steven Godkin <sgodkin_at_Sophia.NCR.COM>
Date: 1 Nov 94 15:43:01 GMT
Message-ID: <5985_at_ncrsoph.Sophia.NCR.COM>


If you always have the same number of entries in B and C for each record in A, then the SQL code would be fairly straightforward. However I am assuming that there will be 3 B's and 2 C's for John Smith, but for Joe Bloggs there could be 12 B's and 1 C. In this case I would recommend a simple PL/SQL program similar to the one below:

DECLARE
   field3 varchar2(30);
   field4 varchar2(30);
   Cursor one is select key_id,field1,field2 from a;    Cursor two (akey_id) is select field3 from b where b.key_id = akey_id;    Cursor three (akey_id) is select field4 from c where c.key_id = akey_id; BEGIN
   FOR arec in one LOOP

        field3 := '';
        field4 := '';
        FOR brec in two (arec.key_id) LOOP
           field3 := field3||brec.field3||',';
        END LOOP;
        FOR crec in three (arec.key_id) LOOP
           field4 := field4||crec.field4||',';
        END LOOP;

"Get rid of the last comma in field3 and field4"
"Print arec.field1, arec.field2, field3 and field4"
  END LOOP;
END; Something like this should suffice in this situation. You could either use the standard output procedures for printing, or insert the information into a temporary table and select from the table after the PLSQL completes.

Steven Godkin Received on Tue Nov 01 1994 - 16:43:01 CET

Original text of this message