Re: please help with query

Use an 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 group by r.room_no;

This will return all the rooms regardless of whether there is a matching user (teacher or assistant). When no user ssn is found, the u1 and u2 columns in the select clause will have NULL values. You can convert them with NVL.

--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
Pornpira Vachareeyanukul wrote in message ...
>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.
>
>Here's what I'm trying to do
>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
> group by r.room_no
>;
>
>
>And I have another question about how to compare 2 counts;
>I have 2 tables
>k_follow which contain msg_id and the follows_id
>msg_id,
>follows_id
>
>and k_group
>msg_id
>news_id - newsgroup that msg is posted to
>
>Message is posted to various newsgroup and I have to compare if the
>follow-up message is posted to more newsgroup than the original message.
>I've been working on it and I've always got error message saying parser
>error.
>Please help me.
>
>Thank you in advance.
>Mimi
>
Received on Wed Dec 09 1998 - 14:38:02 CST