SQL JOIN statement Question.

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?

Thanks,
David

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 16 1999 - 11:20:15 CST