Merge multiple queries by single row and order over all

Question

I have three different queries which have one common row (user_id) that should be combined in one query to apply ordering. It should also work if one or more queries result an empty set.

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
ORDER BY 
    user_id ASC

Query 2:

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

Query 3:

SELECT
    user_id,
    COUNT(*) as logins
FROM
    prognose_activitylog a
WHERE 
    login_time BETWEEN '33333333333' AND '4444444444'
GROUP BY 
    user_id

I would like to have this:

user_id | total_predictions | comments | ccomments | logins
1       | 3                 | 0        | 0         | 7
6       | 6                 | 1        | 3         | 4
7       | 0                 | 0        | 0         | 1

Where "0" would mean there is no data in the table, but the user_id is still available. By this logic I can better work on this data in PHP.

How can this be achieved?

===================================

@Tad: latest query with error "Column 'user_id' in field list is ambiguous"

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 ;

Seems a duplicate of http://stackoverflow.com/questions/4715820/how-to-order-by-with-union — yann.kmm, Mar 21 '12 at 15:31
What do you mean by it didnt' work? You had no results or the results were not the correct ones? Have you tried using the EXPLAIN clause to analyse what went wrong? — yann.kmm, Mar 21 '12 at 17:24
I did, nothing except "Impossible WHERE noticed after reading const tables". I think this comes from my empty result set. — steveo, Mar 21 '12 at 20:21

Teja · Answer 1 · 2012-03-22T17:39:53.490

2

SELECT A.user_id,COALESCE(A.total_predictions,0),B.comments,COALESCE(B.ccomments,0),COALESCE(C.logins,0) FROM 
(
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
) A

INNER JOIN ( 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
) B
ON  A.user_id = B.user_id 
RIGHT OUTER JOIN 
(
SELECT
        user_id,
        COUNT(*) as logins
    FROM
        prognose_activitylog a
     WHERE login_time BETWEEN '33333333333' AND '4444444444'
        GROUP BY user_id
) C
ON  B.user_id = C.user_id
ORDER BY A.user_id;

edited Mar 22 '12 at 17:39

answered Mar 21 '12 at 15:31

Teja

13,214
36
93
155

This results an empty set if f.ex. the third table is empty and also does not return a new column if some rows are empty. I want to use this to create a leaderboard by looking at prediction, comment and login count. – steveo Mar 21 '12 at 15:44
If third table is returning empty then how do you want to see your data? – Teja Mar 21 '12 at 16:47
I would like to have this: user_id | total_predictions | comments | ccomments | logins 1 | 3 | 0 | 0 | 7 6 | 6 | 1 | 3 | 4 7 | 0 | 0 | 0 | 1 Where "0" would mean there is no data in the table, but the user_id is still available. By this logic I can better work on this data in PHP. – steveo Mar 21 '12 at 18:37
Hi Venk, thanks for your answer. However, this modification still does not work. Only when I completly remove the C table and WHERE statements the query displays. In fact, the C table results an empty set, but why does COALESCE not work then? – steveo Mar 21 '12 at 20:05
@steveo Can you tell me in which tables you have data and which tables you don't have. – Teja Mar 22 '12 at 15:40
Of course: I have data in all tables except the "prognose_activitylog" which is "C" in your example... – steveo Mar 22 '12 at 15:46

score 1 · Accepted Answer · edited Mar 22 '12 at 15:23

Hope the following works fine except it's lengthy:

        SELECT [user_id], total_predictions, ISNULL(comments, ''), ISNULL(ccomments, ''), ISNULL(logins, '')
        FROM 
        (
            SELECT [user_id], total_predictions,
                    (
                        --Get comments from Query 2
                        SELECT comments 
                        FROM
                        (
                            SELECT COUNT(*) AS comments
                            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 
                        ) AS Query2
                        WHERE Query1.[user_Id] = Query2.[user_id]
                    ) AS comments,
                    (
                        --Get ccomments from Query2
                        SELECT ccomments  
                        FROM 
                        (
                            SELECT 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 
                        )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 '33333333333' AND '4444444444'         
                            GROUP BY user_id 
                        )AS Query3
                        WHERE Query1.[user_Id] = Query3.[user_id]
                    ) AS logins

            FROM
            (     
                --Get [user_id] 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]  
                --ORDER BY [user_id] ASC   
            )AS Query1
        )AS FinalQuery
        ORDER BY [user_id]

Sorry, I tested the query in MS SQL Server 2005. However, it should be possible to convert this same query to its MySQL equivalent. To do so, one can start with a simplified test query below [from which the above query was derived]:

                    SELECT user_id, total_predictions, ISNULL(comments, '') AS Comments, ISNULL(ccomments, '') AS ccomments, ISNULL(logins,'') AS logins
                    FROM 
                    (
                        SELECT user_id, total_predictions,
                            (
                                --Get comments from Query 2
                                SELECT comments 
                                FROM
                                (
                                    SELECT '1' AS user_id, 'comments' AS comments --Later on replace this with your Query 2 after removing the ORDER BY 
                                ) AS Query2
                                WHERE Query1.user_id =  Query2.user_id
                            ) AS comments,
                            (
                                --Get ccomments from Query2
                                SELECT ccomments  
                                FROM 
                                (
                                    SELECT '1' AS user_id, 'ccomments' AS ccomments --Later on replace this with your Query 2 after removing the ORDER BY 
                                )AS Query2
                                WHERE Query1.user_id =  Query2.user_id
                            ) AS ccomments,
                            (
                                --Get logins from Query3
                                SELECT logins  
                                FROM 
                                (
                                    SELECT '1' AS user_id, 'logins' AS logins  --Later on replace this with your Query 3 after removing the ORDER BY 
                                )AS Query3
                                WHERE Query1.user_id =  Query3.user_id
                            ) AS logins

                        FROM
                        (     
                            --Get product_Num logins and total_predictions from Query 1
                            SELECT '1' AS user_id, 'total_predictions' AS total_predictions -- Later on replace this with your Query 1 after removing the ORDER BY 
                        )AS Query1
                    )AS FinalQuery
                    ORDER BY user_id

The MySQL query version executed from MySQL Command Line Line:

    mysql>       SELECT user_id, total_predictions, IFNULL(comments, '') AS Comments 
 , IFNULL(ccomments, '') AS ccomments, IFNULL(logins,'') AS logins 
  ->       FROM 
  ->       ( 
  ->        SELECT user_id, total_predictions, 
  ->         ( 
  ->          -- Get comments from Query 2 
  ->          SELECT comments 
  ->          FROM 
  ->          ( 
  ->           SELECT '1' AS user_id, 'comments' AS comments -- Later on replace this with your Query 2 after removing the ORDER BY 
  ->          ) AS Query2 
  ->          WHERE Query1.user_id =  Query2.user_id 
  ->         ) AS comments, 
  ->         ( 
  ->          -- Get ccomments from Query2 
  ->          SELECT ccomments 
  ->          FROM 
  ->          ( 
  ->           SELECT '1' AS user_id, 'ccomments' AS ccomments -- Later on replace this with your Query 2 after removing the ORDER BY 
  ->          )AS Query2 
  ->          WHERE Query1.user_id =  Query2.user_id 
  ->         ) AS ccomments, 
  ->         ( 
  ->          -- Get logins from Query3 
  ->          SELECT logins 
  ->          FROM 
  ->          ( 
  ->           SELECT '1' AS user_id, 'logins' AS logins  -- Later on replace this with your Query 3 after removing the ORDER BY 
  ->          )AS Query3 
  ->          WHERE Query1.user_id =  Query3.user_id 
  ->         ) AS logins 
  ->        FROM 
  ->        ( 
  ->         -- Get product_Num logins and total_predictions from Query 1 
  ->         SELECT '1' AS user_id, 'total_predictions' AS total_predictions --  Later on replace this with your Query 1 after removing the ORDER BY 
  ->        )AS Query1 
  ->       )AS FinalQuery 
  ->       ORDER BY user_id 
  -> 
  ->  ; 
  +---------+-------------------+----------+-----------+--------+ 
  | user_id | total_predictions | Comments | ccomments | logins | 
  +---------+-------------------+----------+-----------+--------+ 
  | 1       | total_predictions | comments | ccomments | logins | 
  +---------+-------------------+----------+-----------+--------+ 
  1 row in set (0.06 sec)

FYI, to convert the simplified test query to MySQL query, I made two minor changes: 1) ISNULL to IFNULL (as you pointed out earlier); and 2) The second dash in a MySQL comment should be followed by at least one whitespace. Example: -- Get comments from Query 2. Before conversion, there was no whitespace between "--" and "Get" because the whitespace is optional in MS SQL Server.

Hi tad, I use mySQL. When I replace [user_id] with user_id and replace ISNULL to IFNULL i still get the following error: ` [Error Code: 1054, SQL State: 42S22] Unknown column 'Query2.user_id' in 'where clause'` — steveo, Mar 21 '12 at 18:39
Hi Tad, wow! That's great. It works like a charm after following your instructions. Last question: After implementing the query I noticed that I even need more information to build the leaderboard than only the user_id from a fourth table called prognose_users. After fetching the complete result I would like to add some rows which match the user_ids like user_name, email, etc. Where should this fourth table being queried? The whole query is really complex now. — steveo, Mar 22 '12 at 08:44
Alternative2: Instead of using the INNER JOIN: "INNER JOIN prognose_users ON prognose_users.User_ID = FinalQuery.user_id" put name and email columns to the latest query as follows: SELECT user_id, (SELECT name FROM prognose_users p WHERE p.user_id=FinalQuery.user_id) AS user_name, (SELECT email FROM prognose_users p WHERE p.user_id=FinalQuery.user_id) AS user_email, total_predictions, IFNULL(comments, '0') AS comments , IFNULL(ccomments, '0') AS ccomments, IFNULL(logins,'0') AS logins FROM ( ... ) ... — Tad, Mar 23 '12 at 05:11

score 1 · Answer 3 · answered Mar 22 '12 at 15:02

1

Glad to know that it works for you.

For your latest requirement, I would use INNER JOIN as in the following:

           SELECT user_id, user_name, email, phone, total_predictions, IFNULL(comments, '0') AS Comments   , IFNULL(ccomments, '0') AS ccomments, IFNULL(logins,'0') AS logins  
           FROM 
           (
           -- All the sub queries go here
           )AS FinalQuery
           INNER JOIN prognose_users ON prognose_users.user_id = FinalQuery.user_id

answered Mar 22 '12 at 15:02

Tad

38
3

I merged your two accounts for you. You should register. – Robert Harvey Mar 22 '12 at 15:25
Hi Tad, good idea. I'm getting "Column 'user_id' in field list is ambiguous" now, thought that putting "FinalQuery.user_id" in the SELECT would help, but then I get: "Unknown column 'prognose_users.User_ID' in 'on clause'" altough this column exists in table prognose_users. – steveo Mar 22 '12 at 15:25
Make sure the table name "prognose_users" and field name user_id are correctly spelled out. – Tad Mar 22 '12 at 16:45
Steveo, please execute the following query: SELECT user_id FROM prognose_users; If there is an error, either the table name or the field name is incorrect/misspelled. – Tad Mar 22 '12 at 16:59
Tad, of course I double checked that first. The field name in the prognose_users table is exactly "User_ID". So I did `INNER JOIN prognose_users ON prognose_users.User_ID = FinalQuery.user_id` and also the SELECT you suggested works with `SELECT User_ID FROM prognose_users;` - however when implementing the INNER JOIN I get this error. – steveo Mar 22 '12 at 17:51
Steveo, Could you please post your latest query so that I will be able to reproduce the error: "Unknown column 'prognose_users.User_ID' in 'on clause'"? Thanks. – Tad Mar 22 '12 at 21:04
Tad, yes of course I updated my first post as this does not fit into the comments section. Thanks. – steveo Mar 22 '12 at 21:08
Steveo, this time you have a different error. Earlier you had "Unknown column 'prognose_users.User_ID' in 'on clause'" error. Anyways, to resolve the ambiguous column error, you have to put user_id as FinalQuery.user_id: SELECT FinalQuery.user_id, total_predictions, IFNULL(comments, '0') AS comments , IFNULL(ccomments, '0') AS ccomments, IFNULL(logins,'0') AS logins FROM ( ... )... – Tad Mar 23 '12 at 04:15
Oh sorry, should have known that. Now it works, I even didn't needed your alternative #2. I really like to thank you for your help, I learned so much. Thanks again, Tad! – steveo Mar 23 '12 at 10:48
That is OK. I am glad I could help. Happy codding! – Tad Mar 23 '12 at 14:12

Merge multiple queries by single row and order over all

3 Answers3