I was wondering how I could go by writing a the outer join query to get the required outputs (described below)
where the tables I am outer joining are part of the other join conditions in the statement
given the following datastructure where
– Table A is the main table containing some arbitrary objects
– Table B is referenced by A where A.TYPE_ID = B.ID
– Table C defininfs relations between the objects in Table A where C.SOURCE_ID references A.ID and C.TARGET_ID references A.ID
This is how the schema is defined and I can't do anything about it (it's a legacy system)
TABLE_A
---------------------------
| ID | TYPE_ID | Name |
|-------------------------|
| 1 | 1 | Name 1 |
| 2 | 2 | Name 2 |
| 3 | 1 | Name 3 |
| 4 | 1 | Name 4 |
| 5 | 3 | Name 5 |
|-------------------------|
TABLE_B
----------------------
| ID | TYPE_NAME |
|--------------------|
| 1 | Type 1 |
| 2 | Type 2 |
| 3 | Type 3 |
| 4 | Type 4 |
|--------------------|
TABLE_C
-------------------------------
| PK | SOURCE_ID | TARGET_ID |
|-----------------------------|
| 11 | 2 | 1 |
| 12 | 2 | 3 |
| 13 | 5 | 1 |
| 13 | 5 | 4 |
-------------------------------
What I would like to get is all the objects in Table A of "Type 1" with the name of the object they are associated to (null otherwise) which are of Type 2,
i.e an outer join to get all the objects of Type 1 regardless if they have an association, but if they do then I need the name of the object.
Note that objects of Type 1 will always been in the TARGET in the relstionship.
The output for the above example would be
-------------------------------
| Target Name | Source Name |
|-----------------------------|
| Name 1 | Name 2 |
| Name 3 | Name 2 |
| Name 4 | (NULL) |
|-----------------------------|
My original join query (couldn't get the outer join to work) this is the normal join not showing objects with no associations.
select atrgt.NAME, asrc.NAME
from TABLE_A atrgt
JOIN TABLE_B trgttype on atrgt.TYPE_ID = trgttype.ID
and trgttype.TYPE_NAME = 'Type 1'
JOIN TABLE_C assoc on atrgt.ID = assoc.TARGET_ID
JOIN TABLE_A asrc on asrc.ID = assoc.SOURCE_ID
JOIN TABLE_B srctype on asrc.TYPE_ID = srctype.ID
and srctype.TYPE_NAME = 'Type 2'
Best Answer
Basically in these situations I think the best approach is to subdivide the query into two normal joins, then do the outer join between those results sets. If you think of SQL as procedural code, you may think it looks inefficient, but the query optimizer will not necessarily run the two subjoins independently.
You didn't say what RDBMS you are using. In Oracle I would probably write it like this: