Re: How to change order of joins, help lease

From: Mikael Malmgren <mikael.malmgren_at_biovitrum.com>
Date: 24 Jun 2002 23:36:34 -0700
Message-ID: <1a569fc2.0206242236.6192c7eb_at_posting.google.com>


Actually I'm working as a cheminformatician, and trying to join 8 tables with chemical related data, but stripped it down to three and still got the same problem.
The reason for "homework look" is to not confuse people about what's in there.
To make it simple.
I have three tables, one contains let's say 10 records, 2 other tables has the same number of records let's say 4 each. The two last has the same keys so it's no point outerjoining them. The first one has one key that might be found in the last one. Now retrieve all records from the first one, and also data from the second one (if it's there). This means outerjoin between 1 and 3 and inner join between 2 and 3.

/Micke

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<3DFR8.307316$cQ3.16416_at_sccrnsc01>...
> ?
> 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 Tue Jun 25 2002 - 08:36:34 CEST

Original text of this message