title: MySQL SELECT combining 3 SELECTs INTO 1
body: <p>Consider following tables in MySQL database:</p>
  
  <pre><code>entries:
      creator_id INT
      entry TEXT
      is_expired BOOL
  other:
      creator_id INT
      entry TEXT
  
  userdata:
      creator_id INT
      name VARCHAR
      etc...
  </code></pre>
  
  <p>In <code>entries</code> and <code>other</code>, there can be multiple entries by 1 creator. <code>userdata</code> table is read only for me (placed in other database). </p>
  
  <p>I'd like to achieve a following SELECT result:</p>
  
  <pre><code>+------------+---------+---------+-------+
  | creator_id | entries | expired | other |
  +------------+---------+---------+-------+
  |      10951 |      59 |      55 |    39 | 
  |      70887 |      41 |      34 |   108 | 
  |      88309 |      38 |      20 |   102 | 
  |      94732 |       0 |       0 |    86 |
  </code></pre>
  
  <p>... where <code>entries</code> is equal to <code>SELECT COUNT(entry) FROM entries GROUP BY creator_id</code>,
  <code>expired</code> is equal to <code>SELECT COUNT(entry) FROM entries WHERE is_expired = 0 GROUP BY creator_id</code> and
  <code>other</code> is equal to <code>SELECT COUNT(entry) FROM other GROUP BY creator_id</code>.</p>
  
  <p>I need this structure because after doing this SELECT, I need to look for user data in the "userdata" table, which I planned to do with INNER JOIN and select desired columns.</p>
  
  <p>I solved this problem with selecting "NULL" into column which does not apply for given SELECT:</p>
  
  <pre><code>SELECT
      creator_id,
      COUNT(any_entry) as entries,
      COUNT(expired_entry) as expired,
      COUNT(other_entry) as other
  FROM (
      SELECT
          creator_id,
          entry AS any_entry,
          NULL AS expired_entry,
          NULL AS other_enry
      FROM entries
      UNION
      SELECT
          creator_id,
          NULL AS any_entry,
          entry AS expired_entry,
          NULL AS other_enry
      FROM entries
      WHERE is_expired = 1
      UNION
      SELECT
          creator_id,
          NULL AS any_entry,
          NULL AS expired_entry,
          entry AS other_enry
      FROM other
  ) AS tTemp
  GROUP BY creator_id
  ORDER BY
      entries DESC,
      expired DESC,
      other DESC
  ;
  </code></pre>
  
  <p>I've left out the INNER JOIN and selecting other columns from <code>userdata</code> table on purpose (my question being about combining 3 SELECTs into 1).</p>
  
  <ul>
  <li>Is my idea valid? = Am I trying to use the right "construction" for this?</li>
  <li>Are these kind of SELECTs possible without creating an "empty" column? (some kind of JOIN)</li>
  <li>Should I do it "outside the DB": make 3 SELECTs, make some order in it (let's say python lists/dicts) and then do the additional SELECTs for userdata?</li>
  </ul>
  
  <p>Solution for a <a href="https://stackoverflow.com/questions/2374778/how-do-i-combine-these-sql-select-queries-into-one-select-statement">similar question</a> does not return rows where <code>entries</code> and <code>expired</code> are <code>0</code>.</p>
  
  <p>Thank you for your time.</p>