Re: SQL JOIN statement Question.

the reason you are getting larger result sets is that you c,d,e tables have more then one match for a gievn key.
ie each of c,d,e can have a vaule of 1 or 2 for an id of 1. as a result you get every combination back since you have not specified which you want it gives them all
since the each of c,d,e has 2 value the number of results is 2*2*2 = 8 when you rmove 1 option form c the number of results is 1*2*2= 4

i suspect what you want is results c,d,e for each type

and the following to your where clause

                                 and
     c.type = d.type and
     e.type = c.type

which will give you
ID         A  B  T C  D  E
---------- -- -- - -- -- --
         1 A  B  1 C  D  E
         1 A  B  2 CC DD EE

david_petit_at_yahoo.com wrote in message <80s3o8$8gk$1_at_nnrp1.deja.com>...
>Hello All,
>
> I create the following tables:
>
>create table a (id number(3), a varchar2(2));
>
>create table b (id number(3), b varchar2(2));
>
>create table c (id number(3), type varchar2(1), c varchar2(2));
>
>create table d (id number(3), type varchar2(1), d varchar2(2));
>
>create table e (id number(3), type varchar2(1), e varchar2(2));
>
>insert into a (id,a) values (1,'A');
>insert into b (id,b) values (1,'B');
>insert into c (id,type,c) values (1,'1','C');
>insert into c (id,type,c) values (1,'2','CC');
>insert into d (id,type,d) values (1,'1','D');
>insert into d (id,type,d) values (1,'2','DD');
>insert into e (id,type,e) values (1,'1','E');
>insert into e (id,type,e) values (1,'2','EE');
>
>I want to retrieve the record with the following SQL statement:
>
>select a.ID, a.A, b.B, c.type, c.C, d.D, e.E from A a, B b, C c, D d, E
>e where a.ID = b.ID(+) and a.ID = c.ID(+) and a.ID = d.ID(+) and a.ID =
>e.ID(+);
>
>(return the records based on table A. Should return records in B, C, D
>and E even no matching ID found in these tables)
>
>And get the following result:
>
> ID A B T C D E
> --------- -- -- - -- -- --
> 1 A B 1 C D E
> 1 A B 2 CC D E
> 1 A B 1 C DD E
> 1 A B 2 CC DD E
> 1 A B 1 C D EE
> 1 A B 2 CC D EE
> 1 A B 1 C DD EE
> 1 A B 2 CC DD EE
>
>It seems that duplicate the record set with 4 times! Did anyone tells me
>which part in my SQL statement is wrong? And how to correct this?
>
>Moreover, if no record found in table C, it seems that one record is
>missing. e.g. after I comment the following line:
>
>-- insert into c (id,type,c) values (1,'2','CC');
>
>The result will become:
>
> ID A B T C D E
>--------- -- -- - -- -- --
> 1 A B 1 C D E
> 1 A B 1 C DD E
> 1 A B 1 C D EE
> 1 A B 1 C DD EE
>
>It seems that one record is missing. And how to correct this problem? In
>fact, in the real situation, there are over 20 fields in table C, D and
>E. And each table has over 10000 records. Could anyone tells me whether
>my SQL statement is good (i.e. performance) in retrieving the records?
>
Received on Tue Nov 16 1999 - 14:12:16 CST