title: Joining two tables with sub-queries from one
body: <p>My database features multiple groups that host events at one or more venues. Most times it is just the one venue, but groups will sometimes have two.
  To keep things simple, the following data set is small and imaginary:</p>
  
  <p>'groups' table</p>
  
  <pre><code>+--------------+--------------+--------------+
  | name         | pri_venue_id | alt_venue_id |
  +--------------+--------------+--------------+
  | Fast Fingers |            3 |            0 |
  | Data Dishers |            4 |            0 |
  | Leet Hacks   |            5 |            2 |
  +--------------+--------------+--------------+
  </code></pre>
  
  <p>'venues' table</p>
  
  <pre><code>+----------+-------------------------+-----------------+
  | venue_id | name                    | location        |
  +----------+-------------------------+-----------------+
  |        1 | Public Archives         | Querytown       |
  |        2 | Storage Function Centre | Drive Bay       |
  |        3 | Key Convention Centre   | Qwertyville     |
  |        4 | Head-spin Mall          | Drive Park      |
  |        5 | Fast Storage Facility   | Memory Bay      |
  +----------+-------------------------+-----------------+
  </code></pre>
  
  <p>I want to join the two tables together in a single query, such that the result of the query will display the group, the primary venue name and location, and the second venue and location if there is one.
  In the case that there is no second/alternative venue, just show the primary, then two NULLS, like so:</p>
  
  <pre><code>Data Dishers, Head-spin Mall, Drive Park, NULL, NULL
  Fast Fingers, Key Convention Centre, Qwertyville, NULL, NULL
  Leet Hacks, Fast Storage Facility, Memory Bay, Storage Function Centre, Drive Bay
  </code></pre>
  
  <p>Please note that the result set does not have to be comma-delimited.</p>
  
  <p>I have read other stackoverflow questions, but have not yet found a solution. I keep getting either duplicate rows, or else everything but the alternative venue, which always displays NULL. I'm not sure what would be best to group by, as I saw this suggested in another question. Also tried using a union, but could not seem to get it to work.</p>
  
  <p>Here is the closest query that I have come up with:</p>
  
  <pre><code>SELECT 
    DISTINCT groups. NAME,
      x. NAME,
      x.location,
      y. NAME,
      y.location
  FROM groups
  INNER JOIN (
      SELECT
          venues.*
      FROM venues
      INNER JOIN groups ON venue_id = pri_venue_id
  ) AS x,
  groups
  INNER JOIN (
      SELECT
          venues.*
      FROM venues
      LEFT JOIN groups ON venue_id = alt_venue_id
  ) AS y
  GROUP BY x.venue_id
  ORDER BY groups. NAME;
  </code></pre>
  
  <p>Thanks in advance.</p>