SQL spooling two tables to one spoolfile ??

From: Anders Nilsson <anders.nilsson_at_mbox10.swipnet.se>
Date: Sat, 16 Jan 1999 09:55:25 GMT
Message-ID: <hmZn2.30370$9S3.76941@nntpserver.swip.net>

I have two tables with following columns....

    TABLE CUSTOMER
    customer_id
    first_name
    sur_name

    TABLE TELEPHONE_NO
    telephone_nr_id
    customer_id
    usage_id (where 1=homephone, 2=workphone, 3=mobile phone and so on)     area_code
    phone_no

that I want to spool to a file in this format:

    JOINED TABLES
    cust_no first_name sur_name area_1 phone_1 area_2 phone_2 area_3 phone_3

• ---------- --------- ------- -------- ------- -------- ------- ------- 0001 Anders Nilsson 123 823421 123 823422 123 823423 0002 Sven Svensson 134 123456 123 123457

and I can do this with the following script:

spool c:\temp.txt

SELECT customer.customer_id cust_no,
       customer.first_name,
       customer.sur_name,
       tmp_phone1.area_code area_1,
       tmp_phone1.phone_no phone_1,
       tmp_phone2.area_code area_2,
       tmp_phone2.phone_no phone_2,
       tmp_phone3.area_code area_3,
       tmp_phone3.phone_no phone_3
FROM   customer,
       telephone_no tmp_phone1,
       telephone_no tmp_phone2,
       telephone_no tmp_phone3
WHERE  customer.customer_id = tmp_phone1.customer_id AND
       customer.customer_id = tmp_phone2.customer_id AND
       customer.customer_id = tmp_phone3.customer_id AND
       tmp_phone1.usage_id = 1 AND
       tmp_phone2.usage_id = 2 AND
       tmp_phone3.usage_id = 3

;
spool off

The problem is that customer 0002 wont appear in the spoolfile since he doesn't have the third telephonenumber.

How do I solve this problem with PL/SQL?

Thank's in advance!

Anders

PS:

Some example data:

EXAMPLE CUSTOMER
customer_id first_name sur_name address

0001         Anders      Nilsson   Testgatan 1
0002         Sven        Svensson  Testgatan 18

EXAMPLE TELEPHONE_NO
telephone_nr_id	customer_id  usage_id  area_code  phone_no
100001          001          1         123        823421
100002          001          2         123        823422
100003          001          3         123        823423
100004          002          1         134        123456
100005          002          2         123        123457

Received on Sat Jan 16 1999 - 03:55:25 CST