How can I do it in SQL?

From: Anton V. Gruza <ant_at_geol.spb.su>
Date: Fri, 23 Sep 1994 17:10:33
Message-ID: <780325833466282_at_arcom.spb.su>

Hello, world!
Maybe my question isn't advanced, but I can't find a decision. I have three tables:

 HOME( ID,
       <HOME_ADDRESS> );

 PEOPLE( ID,

         NAME,
         HOME ( REFERENCES HOME(ID) ) );

 FRIENDSHIP_BETWEEN( ID_FROM ( REFERENCE( PEOPLE( ID ) ),
                     ID_TO ( REFERENCES( PEOPLE( ID ) ) );

 For example:
 HOME:

 1   <St.Petersburg, Nevskii pr., 20>
 2   <.......>
 3   <.......>

 PEOPLE                           FRIENDSHIP_BETWEEN
 1   Anton  1                     1 (Anton)   2(Peter)
 2   Peter  1                     1 (Anton)   4(Alex)
 3   George 1                     2 (Peter)   4(Alex)

 4 Alex 2
 5 Sam 2

 I want to made outer join, such that I can see all people living at home 1, with check - if this person is in friendship with Alex.

 I try:
 SELECT PEOPLE.ID, PEOPLE.NAME, FRIENDSHIP_BETWEEN.ID_FROM  FROM PEOPLE, FRIENDSHIP_BETWEEN
 WHERE PEOPLE.HOME = 1 AND PEOPLE.ID = FRIENDSHIP_BETWEEN.ID_FROM(+) AND        FRIENDSHIP_BETWEEN.ID_TO = 4;  And I got only:

 1 Anton 1
 2 Peter 2

 But I want to see "3 George NULL"!!!

 Thanks in advance.
 Anton

---

 Anton Gruza                       E-mail:   ant_at_geol.spb.su
 North-Western Geological Center   Voice:    (812)352-30-19
 St.Petersburg, Russia             Fax:      (812)352-3019


___________________________________________________________________________

Received on Fri Sep 23 1994 - 17:10:33 CEST