sql question

I have a sql question. I think there may be a way to do this in one straight join instead of creating a 'cleaner' table. Any ideas? TIA.

First, the information:
SQL> desc phonetest

 Name                            Null?    Type
 ------------------------------- -------- ----
 USER_ID                                  NUMBER(10)
 PTYPE_ID                                 NUMBER(1)
 PHONE_NO                                 CHAR(10)

SQL> desc persontest
 Name                            Null?    Type
 ------------------------------- -------- ----
 USER_ID                                  NUMBER(10)
 B_ID                                     NUMBER(10)
 L_ID                                     NUMBER(10)
 L_DATE                                   DATE

contents

SQL> select * from persontest;

  USER_ID B_ID L_ID L_DATE

--------- --------- --------- ---------
        1       100         1 02-JAN-98
        2       100         2 03-JAN-98
        3       200         1 07-MAR-98

SQL> select * from phonetest;

  USER_ID PTYPE_ID PHONE_NO
--------- --------- ----------

        1         1 2015551212
        1         2 2125551212
        2         1 7185551212

Processing ...
select pert.user_id,font.phone_no from
persontest pert, phonetest font
where
pert.user_id = font.user_id(+)

USER_ID PHONE_NO
---------- ----------

1          2015551212
1          2125551212
2          7185551212

4 rows retrieved

HERE IS WHAT i NEED:

I need to retrieve previous query, BUT only where ptype_id= 1. If I do that now, I get

select pert.user_id,font.phone_no from
persontest pert, phonetest font
where
pert.user_id = font.user_id(+)
and
font.ptype_id = 1
USER_ID PHONE_NO
---------- ----------

1          2015551212
2          7185551212

2 rows retrieved

and the user_id 3 is lost because there is no one-to-one set correlation.

Can anyone please help me write an outer join that puts the user_id=3 into the resultset
but also checks for ptype_id = 1 in phonetest?

thanks,
vic Received on Wed Apr 22 1998 - 11:34:18 CDT