Re: How to change order of joins, help lease

?
Trying to join on age and street? So you are looking for anyone who has a dog the same age as them?
This smacks of a homework assignment, is it? Jim
"Mikael Malmgren" <mikael.malmgren_at_biovitrum.com> wrote in message news:1a569fc2.0206240053.333ab1cc_at_posting.google.com...
> Hi.
> I'm trying to figure out how to change the order of joins, but no
> luck.
> Is there someone who can explain the following to me.
>
> I have three example tables
>
> create table test1 (age number, name varchar2(10))
> create table test2 (name varchar2(10), street varchar2(10))
> create table test3 (street varchar2(10), dog varchar2(10), age number)
>
> insert into test1 values(32,'Micke');
> insert into test1 values(30,'Mats');
> insert into test1 values(28,'John');
> insert into test1 values(35,'Anders');
>
> insert into test2 values('John',1);
> insert into test2 values('Mats',2);
> insert into test2 values('Micke',3);
>
> insert into test3 values (1,'Wolf',28);
> insert into test3 values (2,'Pudel',30);
> insert into test3 values (3,'Tax',32);
>
> /* Get all ages of the people and also if the have dogs display their
> names */
>
> select test1.age,test2.name
> from
> test1,test3,test2
> where
> test1.age = test3.age (+) and
> test2.street = test3.street;
>
> Result.
> Age Name
> 28 John
> 30 Mats
> 32 Micke
>
> /* Why don't I get 35 ? */
> /* I can get 35 if I also outer join test2 with test3 but why, since
> they both have all the keys ? */
> select test1.age,test2.name
> from
> test1,test3,test2
> where
> test1.age = test3.age (+) and
> test3.street = test2.street (+);
>
> Result.
> Age Name
> 28 John
> 30 Mats
> 32 Micke
> 35
>
>
> Help please
Received on Mon Jun 24 2002 - 16:01:35 CEST