LEFT OUTER JOIN problem with three linked tables

From: Martin <dr.martin_at_home.se>
Date: 14 Apr 2003 08:02:02 -0700
Message-ID: <ab80865c.0304140702.1517e45d@posting.google.com>

I have the following table structure:

+---------+              +---------+                


| TABLE_1 |              | TABLE_2 |                +---------+

+---------+   0      n   +---------+                | TABLE_3 |


| ID      | <----------> | T1_ID   |   1        1   +---------+

| ...     |              | T3_ID   | <------------> | ID      |

| ...     |              +---------+                | ...     |

+---------+                                         | ...     |
                                                    +---------+

i.e. TABLE_1 holds a column called ID which is mapped to TABLE_2. TABLE_3 also holds a column called ID which also is mapped to TABLE_2. For each row in TABLE_1, there can be several corresponding rows in TABLE_3 (and TABLE_2) but there might also be rows in TABLE_1 that does not have a corresponding row in TABLE_3.

e.g. TABLE_1 has four rows with ID={1,2,3,4} and TABLE_3 has two rows with ID={11,12,13}. The links between the ID's can be viewed in TABLE_2: +------------+ +---------------+---------------+ +------------+
| TABLE_1.ID | | TABLE_2.T1_ID | TABLE_2.T3_ID | | TABLE_3.ID |
+------------+ +---------------+---------------+ +------------+
| 1 | | 1 | 11 | | 11 |
| 2 | | 2 | 12 | | 12 |
| 3 | | 2 | 13 | | 13 |
| 4 | +---------------+---------------+ +------------+
+------------+

I want to query the database so I get all ID's from TABLE_1 and their corresponding values from TABLE_3. If there are none, I should get NULL in that column, something like the following:

+------------+------------+
| TABLE_1.ID | TABLE_3.ID |

+------------+------------+
| 1 | 11 |
| 2 | 12 |
| 2 | 13 |
| 3 | NULL |
| 4 | NULL |

+------------+------------+

I've been trying to do it with LEFT join but always ends up with duplicates or the null value rows not listed.

Any help is appreciated

Thanks

\Martin Received on Mon Apr 14 2003 - 10:02:02 CDT