title: SQL JOIN returning both tables in single column
body: <p>How do I do a SQL JOIN returning both tables in single column?</p>
  
  <p>For example, for a given group ID, how do I obtain a single list of all people who belong to that group plus all their friends?</p>
  
  <p>groups</p>
  
  <pre><code>-id
  -name
  </code></pre>
  
  <p>groups_have_people</p>
  
  <pre><code>-groups_id
  -people_id
  </code></pre>
  
  <p>people</p>
  
  <pre><code>-id
  -name
  -group_id
  </code></pre>
  
  <p>people_have_friends</p>
  
  <pre><code>-people_id1 (references people.id)
  -people_id2 (references people.id)
  </code></pre>
  
  <p>For instance, <code>groups_have_people</code> includes <code>people_id</code> 1, 2, and 3 for group 123, and <code>people_have_friends</code> includes records <code>1-2</code>, <code>1-3</code>, <code>1-4</code>, <code>2-1</code>,<code>2-5</code>, I should get the following results:</p>
  
  <pre><code>id Name
  1  Mary
  2  John
  3  Burt
  4  Jill
  5  Sue
  </code></pre>
  
  <p>I can do something like the following, but it doesn't return a single list.</p>
  
  <pre><code>SELECT p1.*, p2.*
  FROM people p1
  INNER groups_have_people ghp ON ghp.people_id=p1.id
  LEFT OUTER JOIN people_have_friends phf ON p1.id=phf.people_id1
  LEFT OUTER JOIN people p2 ON p2.id=phf.people_id2
  WHERE ghp.groups_id=123;
  </code></pre>
  
  <p><strong>EDIT</strong>  Looks like maybe I don't use a JOIN to do so, and should do something like the following?</p>
  
  <pre><code>SELECT p.*
  FROM people p
  INNER groups_have_people ghp ON ghp.people_id=p.id
  WHERE ghp.groups_id=123
  UNION
  SELECT people p1
  INNER JOIN people_have_friends phf ON p1.id=phf.people_id1
  INNER JOIN people p2 ON p2.id=phf.people_id2
  WHERE p2.groups_id=123;
  </code></pre>
  
  <p><strong>EDIT 2.  Add more sample data</strong></p>
  
  <p>Groups</p>
  
  <pre><code>id  name
  111 Green
  123 Blue
  222 Yello
  </code></pre>
  
  <p>People</p>
  
  <pre><code>id Name
  1  Mary
  2  John
  3  Burt
  4  Jill
  5  Sue
  6  Bob
  7  Wilma
  </code></pre>
  
  <p>groups_have_people</p>
  
  <pre><code>groups_id  people_id
  111        1
  111        5
  123        1
  123        2
  123        3
  222        3
  222        7
  </code></pre>
  
  <p>people_have_friends</p>
  
  <pre><code>people_id1  people_id2
  1           2
  1           3
  1           4
  2           1
  2           5
  4           5
  5           6
  </code></pre>
  
  <p><strong>EDIT 3.  What about something like the following?</strong></p>
  
  <pre><code>SELECT DISTINCT id,
                  name
  FROM people
  WHERE id IN
      ( SELECT people_id
       FROM groups_have_people
       WHERE groups_id=123
       UNION SELECT phf.people_id
       FROM people_have_friends phf
       INNER JOIN groups_have_people ghp ON ghp.people_id=phf.people_id
       WHERE ghp.groups_id=123);
  </code></pre>
  
  <p>or</p>
  
  <pre><code>SELECT DISTINCT id,
                  name
  FROM people
  INNER JOIN
    (SELECT people_id
     FROM groups_have_people
     WHERE groups_id=123
     UNION SELECT phf.people_id
     FROM people_have_friends phf
     INNER JOIN groups_have_people ghp ON ghp.people_id=phf.people_id
     WHERE ghp.groups_id=123) x ON x.people_id=people.id;
  </code></pre>