Re: SQL query for M2M relationship

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Wed, 2 Feb 2000 12:14:02 +0100
Message-ID: <8795a7$2i3m$1@news4.isdnet.net>

If you want a symmetrical output, you have to use a union:

create table tbl_a (a_id number, a_name varchar2(20));
create table tbl_b (b_id varchar2(2), b_name varchar2(20));
create table tbl_c (a_id number, b_id varchar2(2));
insert into tbl_a values (1, 'JOE');
insert into tbl_a values (2, 'BOB');
insert into tbl_a values (3, 'TOM');
insert into tbl_a values (4, 'RON');
insert into tbl_b values ('X', 'SALLY');
insert into tbl_b values ('Y', 'JANE');
insert into tbl_b values ('Z', 'MARY');
insert into tbl_b values ('T', 'RACHEL');
insert into tbl_c values (1, 'X');
insert into tbl_c values (1, 'Y');

insert into tbl_c values (3, 'Z');
insert into tbl_c values (4, 'Z');
commit;

select A.A_NAME "A name", nvl(B.B_NAME, '<null>') "B name" from TBL_A A, TBL_B B, TBL_C C
where A.A_ID = C.A_ID (+)
and B.B_ID (+) = C.B_ID
union
select nvl(A.A_NAME, '<null>'), B.B_NAME from TBL_A A, TBL_B B, TBL_C C
where A.A_ID (+) = C.A_ID
and B.B_ID = C.B_ID (+)
/

A name B name

-------------------- --------------------
<null>               RACHEL
BOB                  <null>
JOE                  JANE
JOE                  SALLY
RON                  MARY
TOM                  MARY

6 rows selected.

--
Have a nice day
Michel

K Raza <ELN/Kraza_at_earthlink.net> a écrit dans le message : pJKl4.341$DK2.9309_at_newsread1.prod.itd.earthlink.net...
> You may try the folowing SQL:
>
> select A.A_NAME, B.B_NAME
> from TBL_A A, TBL_B B, TBL_C C
> where A.A_ID = C.A_ID (+)
> and B.B_ID (+) = C.B_ID
>
>
>
> "Todd Owers" <toddowers_at_my-deja.com> wrote in message
> news:877oeo$81l$1_at_nnrp1.deja.com...
> > I need help constructing a query to return all the rows of two tables
> > that have a many-to-many relationship.
> >
> > Here is the table layout. TBL_A and TBL_B are the substantive tables,
> > and TBL_C is the intermediate table that holds the primary keys from
> > TBL_A and TBL_B.
> >
> > TBL_A has two columns, A_ID and A_NAME, with the following data:
> > A_ID A_NAME
> > ==== ======
> > 1 JOE
> > 2 BOB
> > 3 TOM
> > 4 RON
> >
> > TBL_B has two columns, B_ID and B_NAME, with the following data:
> > B_ID B_NAME
> > ==== ======
> > X SALLY
> > Y JANE
> > Z MARY
> >
> > TBL_C has two columns, A_ID and B_ID, with the following data:
> > A_ID B_ID
> > ==== ====
> > 1 X
> > 1 Y
> > 3 Z
> > 4 Z
> >
> > I want to construct a query that returns the following:
> > A_NAME B_NAME
> > ====== ======
> > JOE SALLY
> > JOE JANE
> > BOB <null>
> > TOM MARY
> > RON MARY
> >
> > I think I need an an outer join between TBL_A and TBL_C, but I am
> > having trouble constructing the query to return the desired result set.
> > My result set does not have the row with A_NAME = BOB and B_NAME =
> > <null>. (No, this is not a homework assignment; I have abstracted the
> > elements of my real-world situation.)
> >
> > Thanks in advance for your help.
> >
> > Todd Owers
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
>
Received on Wed Feb 02 2000 - 05:14:02 CST