Re: please help with query

Pornpira Vachareeyanukul wrote:
>
> Hi
> I need some help with the query statement;
>
> table user
> ssn varchar2(9),
> first_name varchar2(20),
> last_name varchar2(20)
>
> table room
> room_no varchar2(10),
> teacher_ssn varchar2(9),
> teacher_assistant_ssn varchar2(9)
>
> I'm trying to select all classrooms from room table and get the teacher
> and teacher assistant first and last name. I'm having the problem writing
> query so that just in case of there's teacher_ssn in the room table but no
> ssn in the user table and I can get that room eventhough there's no record
> of that teacher in the user table.
>
>

Hi

you have to use an OUTER-Join

which brings the records which cannot be connected by JOIN because in one of the tables the informatioon is missing

Look at the + sign in the statement.

I'm not shure that the + has to be on the right or the lefz side of the where clause

Refer to the manual -> Outer Join

Select r.room_no, u1.first_name, u1.last_name,

       u2.first_name, u2.last_name
  from room r, user u1, user u2
 where r.teacher_ssn = u1.ssn (+)
   and r.teacher_assistant_ssn = u2.ssn (+) Received on Wed Dec 09 1998 - 01:44:40 CST