Xref: alice comp.databases.oracle.server:83026 Path: alice!news-feed.fnsi.net!newsfeed.icl.net!newspeer.clara.net!news.clara.net!fr.clara.net!isdnet!isdnethub!not-for-mail From: "Michel Cadot" Newsgroups: comp.databases.oracle.server Subject: Re: SQL query for M2M relationship Date: Wed, 2 Feb 2000 12:14:02 +0100 Lines: 116 Message-ID: <8795a7$2i3m$1@news4.isdnet.net> References: <877oeo$81l$1@nnrp1.deja.com> Reply-To: "Michel Cadot" X-Trace: news4.isdnet.net 949491847 84086 195.132.3.203 (2 Feb 2000 11:44:07 GMT) X-Complaints-To: abuse@isdnet.net NNTP-Posting-Date: 2 Feb 2000 11:44:07 GMT X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 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, '') "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, ''), 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 -------------------- -------------------- RACHEL BOB JOE JANE JOE SALLY RON MARY TOM MARY 6 rows selected. -- Have a nice day Michel K Raza a écrit dans le message : pJKl4.341$DK2.9309@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" wrote in message > news:877oeo$81l$1@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 > > 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 = > > . (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. > > > >