SQL query for M2M relationship

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 - 16:58:35 CST