title: Left Outer join with same table as part of the outer join
body: <p>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</p>
  
  <p>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</p>
  
  <p>This is how the schema is defined and I can't do anything about it (it's a legacy system)</p>
  
  <pre><code>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         |
  -------------------------------
  </code></pre>
  
  <p>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.</p>
  
  <p>The output for the above example would be</p>
  
  <pre><code>-------------------------------
  | Target Name | Source Name   |
  |-----------------------------|
  | Name 1      | Name 2        |
  | Name 3      | Name 2        |
  | Name 4      | (NULL)        |
  |-----------------------------|
  </code></pre>
  
  <p>My original join query (couldn't get the outer join to work) this is the normal join not showing objects with no associations.</p>
  
  <pre><code>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'
  </code></pre>