title: Fetching multiple rows by foreign key column
body: <p>I have an Address table that contains multiple addresses </p>
  
  <pre><code>Address: 
  | Id   |   details   | ... |  
   - - - - - - - - -  - - - - - - - 
  | 1    |  details |    ...        |
  | 2    |  details |    ...        |
  | 3    |  details |    ...        |
  | 4    |  details |    ...        |
  </code></pre>
  
  <p>Then i have a site table, that contains address.id as FK to represent Billing or Shipping address, </p>
  
  <pre><code>Site: 
  | Id   |   details   | Ship_Address_id  |  Bill_Address_id | ... |
   - - - - - - - - -  - - - - - - - 
  | 1    |  details |          1           |        2          |     |
  | 2    |  details |          1           |        3          |     |  
  | 3    |  details |          4           |        4          |     |
  | 4    |  details |          2           |        3          |     |
  </code></pre>
  
  <p>is there a way to join both the tables so that single row from site table may fetch two rows of address table, even in the case of same address of both columns. 
  i was expecting to use multiple join of site with address but that is not working:</p>
  
  <p>This is what i tried:</p>
  
  <pre><code>SELECT CASE 
          WHEN ship.CRM_Address_Internal_Id_Ship IS NOT NULL THEN 'Ship'
          WHEN bill.CRM_Address_Internal_Id_Ship IS NOT NULL THEN 'Bill'
          ELSE '' END 
  FROM Address as Adr
  LEFT JOIN Site ship ON ship.Ship_Address_id = Adr.id 
  LEFT JOIN Site bill ON bill.Bill_Address_id = Adr.id    
  </code></pre>
  
  <p>Even if i don't use the CASE it does not fetch two rows for each address with respect to site.</p>
  
  <p>Please advise.  </p>
  
  <p><strong>EDIT</strong>
  Here is the desired output table:
  as in Site table we have two different ids of address table (Ship/Bill) so the result table should display two rows for each site.id<br>
  for example i'm fetching records for site.id 1 and 3 then</p>
  
  <pre><code>Result_Table:
  | Address.ID | Address Details | TYPE | ... |
  --------------------------------------------
  | 1         | Other Details    | Ship | ... |
  | 2         | Other Details    | Bill | ... |
  | 4         | Other Details    | Ship | ... |
  | 4         | Other Details    | Bill | ... |
  </code></pre>