Re: SQL query for M2M relationship

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 Tue Feb 01 2000 - 18:18:29 CST