title: Merge multiple queries by single row and order over all
body: <p>I have three different queries which have one common row (<code>user_id</code>) that should be combined in one query to apply ordering. It should also work if one or more queries result an empty set.</p>
  
  <p>Query 1:</p>
  
  <pre><code>SELECT
      user_id,
      COUNT(answer) AS total_predictions    
  FROM
      prognose_predictions pc
  INNER JOIN prognose_prognose pp
      ON pp.prognose_id = '9'
      AND pc.prognose_id = '9'
      AND pc.prognose_id = pp.prognose_id
  GROUP BY 
      user_id
  ORDER BY 
      user_id ASC
  </code></pre>
  
  <p>Query 2:</p>
  
  <pre><code>SELECT
      user_id,
      COUNT(*) AS comments,
      ccomments
  FROM 
  (
      SELECT
          COUNT(*) AS ccomments
      FROM
          prognose_ccomments cc
      LEFT JOIN prognose_comments p
          ON cc.post_id = p.p_id
  )   AS tmp_table,
      prognose_comments c
  INNER JOIN prognose_prognose x
      ON x.prognose_id = c.prognose_id AND c.prognose_id = 9
  GROUP BY
      c.user_id
  ORDER BY 
      c.user_id ASC
  </code></pre>
  
  <p>Query 3:</p>
  
  <pre><code>SELECT
      user_id,
      COUNT(*) as logins
  FROM
      prognose_activitylog a
  WHERE 
      login_time BETWEEN '33333333333' AND '4444444444'
  GROUP BY 
      user_id
  </code></pre>
  
  <p>I would like to have this:</p>
  
  <pre><code>user_id | total_predictions | comments | ccomments | logins
  1       | 3                 | 0        | 0         | 7
  6       | 6                 | 1        | 3         | 4
  7       | 0                 | 0        | 0         | 1
  </code></pre>
  
  <p>Where "0" would mean there is no data in the table, but the <code>user_id</code> is still available.
  By this logic I can better work on this data in PHP.</p>
  
  <p>How can this be achieved?</p>
  
  <p>===================================</p>
  
  <p>@Tad: latest query with error "Column 'user_id' in field list is ambiguous"</p>
  
  <pre><code>SELECT
              user_id,
              total_predictions,
              IFNULL(comments, '0')  AS comments ,
              IFNULL(ccomments, '0') AS ccomments,
              IFNULL(logins,'0')     AS logins
          FROM
              (
                  SELECT
                      user_id,
                      total_predictions,
                      (
                          -- Get comments from Query 2
                          SELECT
                              comments
                          FROM
                              (
                                  SELECT
                                      COUNT(*) AS comments,
                                      user_id
                                  FROM
                                      (
                                          SELECT
                                              COUNT(*) AS ccomments
                                          FROM
                                              prognose_ccomments cc
                                          LEFT JOIN
                                              prognose_comments p
                                          ON
                                              cc.post_id = p.p_id ) AS tmp_table,
                                      prognose_comments c
                                  INNER JOIN
                                      prognose_prognose x
                                  ON
                                      x.prognose_id = c.prognose_id
                                  AND c.prognose_id = '9'
                                  GROUP BY
                                      c.user_id ) AS Query2
                          WHERE
                              Query1.user_id = Query2.user_id ) AS comments,
                      (
                          -- Get ccomments from Query2
                          SELECT
                              ccomments
                          FROM
                              (
                                  SELECT
                                      ccomments,
                                      user_id
                                  FROM
                                      (
                                          SELECT
                                              COUNT(*) AS ccomments
                                          FROM
                                              prognose_ccomments cc
                                          LEFT JOIN
                                              prognose_comments p
                                          ON
                                              cc.post_id = p.p_id ) AS tmp_table,
                                      prognose_comments c
                                  INNER JOIN
                                      prognose_prognose x
                                  ON
                                      x.prognose_id = c.prognose_id
                                  AND c.prognose_id = '9'
                                  GROUP BY
                                      c.user_id )AS Query2
                          WHERE
                              Query1.user_id = Query2.user_id ) AS ccomments,
                      (
                          -- Get logins from Query3
                          SELECT
                              logins
                          FROM
                              (
                                  SELECT
                                      user_id,
                                      COUNT(*) AS logins
                                  FROM
                                      prognose_activitylog a
                                  WHERE
                                      login_time BETWEEN '1332284401' AND '1333058399'
                                  GROUP BY
                                      user_id )AS Query3
                          WHERE
                              Query1.user_id = Query3.user_id ) AS logins
                  FROM
                      (
                          -- Get product_Num logins and total_predictions from Query 1
                          SELECT
                              user_id,
                              COUNT(answer) AS total_predictions
                          FROM
                              prognose_predictions pc
                          INNER JOIN
                              prognose_prognose pp
                          ON
                              pp.prognose_id = '9'
                          AND pc.prognose_id = '9'
                          AND pc.prognose_id = pp.prognose_id
                          GROUP BY
                              user_id )AS Query1 )AS FinalQuery
                                                      INNER JOIN prognose_users ON prognose_users.User_ID = FinalQuery.user_id
          ORDER BY
              total_predictions DESC,
              comments DESC,
              ccomments DESC,
              logins DESC LIMIT 0,10 ;
  </code></pre>