Re: How to change order of joins, help lease

Your problem has nothing to do with the join order. Try this:

select chem_info.regno ,mix.compound_id
from chem_info,(select batch.regno,compound.compound_id from compound,batch

                 where batch.batch = compound.batch) mix

mikael.malmgren_at_biovitrum.com (Mikael Malmgren) wrote in message news:<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 Jul 24 2002 - 08:02:44 CEST