Re: How to change order of joins, help lease

From: Mikael Malmgren <mikael.malmgren_at_biovitrum.com>
Date: 26 Jun 2002 00:03:14 -0700
Message-ID: <1a569fc2.0206252303.429b9397_at_posting.google.com>

Sorry for the confusing data.
Here is a clearer example.

create table chem_info (regno number,submitter varchar2(10)); create table compound (compound_id number, batch number); create table batch (batch number, regno number);

insert into chem_info values(1,'Micke');
insert into chem_info values(2,'Mats');
insert into chem_info values(3,'John');
insert into chem_info values(4,'Thomas');

insert into compound values(456,1);

insert into compound values(457,2);
insert into compound values(458,3);

insert into batch values(1,1);
insert into batch values(2,2);
insert into batch values(3,3);

/* Get all regnos in chem_info and show me if they have a compound_id */

select chem_info.regno,compound.compound_id from chem_info,compound,batch
where
chem_info.regno = batch.regno (+) and
batch.batch = compound.batch

Result:
Regno Compound_id

1      456
2      457
3      458

This should (in my opinion) give me all the four rows from chem_info together with compound_id for regno 1,2 and 3 and null compound_id for regno 4.
I can get it with adding outer join on the relation between compound and batch, but that doesnt make sense, since batch and compound has the same rows and keys.

I've tried with primary foreign keys, hinting on ORDERED, nested loops, etc but to no help.

Regards,
Micke

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<VzZR8.65156$EP.6912_at_sccrnsc03>...
> 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 Wed Jun 26 2002 - 09:03:14 CEST