Re: How to change order of joins, help lease

outer join just means if there is NOT a matching record in the child table show me the parent record anyway. An inner join will only show a record if there is a parent and a child record.

The thing in your example is that you are joining on ages and I am assuming one is the dog's age and one is the owner's age and that doesn't make sense. Do you have any foreign key relationships between these tables? I think a small example of what you are trying to do - the actual 8 tables - would help.

Jim
"Mikael Malmgren" <mikael.malmgren_at_biovitrum.com> wrote in message news: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 - 14:43:34 CEST