Question 341720 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to join three queries with Union or any other way?

I have two tables and three queries. I want to count the number of rows in one table, the number of different rows in the second table and then Order by the average of the three. I tried using UNION but it seems like I only get the first query working. I used the code given on the first answer and it actually works. What i wanna do is get all the users with their count,count2 and count3, and list them on the table

This code just gets the info for one person. I wan the three queries to get the info based on the number of users.

Here’s my query (UPDATED):

SELECT * 
FROM ( SELECT * FROM users) t1
JOIN ( SELECT COUNT(*) AS count2 FROM posts WHERE  `type` = 'Q' ) t2
JOIN ( SELECT COUNT(*) AS count3 FROM posts WHERE `type` = 'A' ) t3
JOIN ( SELECT COUNT(*) AS count FROM article_history) t4
ORDER BY (t4.count + t2.count2 + t3.count3) / 3 DESC

What I want to get is all the users with those info order from the highest average to lowest but what i get is 3 different users all with the same info.

The above code gives an output like this which is not what i expect:

# Username count count2 count3
1 Jake 9 33 2
2 James 9 33 2
3 John 9 33 2
Tags:

2

Answers


  1. 0

    The main problem is you are using UNION and the UNION works with same column or same alias name but you are using the different alias name for each like count, count2, count3

    <?php
$sql = "SELECT * 
    FROM ( SELECT COUNT(*) AS count FROM history WHERE user ='$userid' ) t1
    JOIN ( SELECT COUNT(*) AS count2 FROM posts WHERE userid ='$userid' AND `type` = 'Q' ) t2
    JOIN ( SELECT COUNT(*) AS count3 FROM posts WHERE userid ='$userid' AND `type` = 'A' ) t3
    ORDER BY (t1.count + t2.count2 + t3.count3) / 3 DESC
";

    This way, you can get the counts from each table and order the results based on the calculated average of the counts.

    Login or Signup to reply.
  2. 0

    After you have completely changed the query in your request, it seems you want to select all users with their respective counts and use these counts to order the userid rows.

    WITH
  questions AS
  (
    SELECT userid, COUNT(*) AS question_count
    FROM posts
    WHERE `type` = 'Q'
    GROUP BY userid
  ),
  answers AS
  (
    SELECT userid, COUNT(*) AS answer_count
    FROM posts
    WHERE `type` = 'A'
    GROUP BY userid
  ),
  history AS
  (
    SELECT userid, COUNT(*) AS history_count
    FROM article_history
    GROUP BY userid
  )
SELECT *
FROM users u
LEFT OUTER JOIN questions q USING (userid)
LEFT OUTER JOIN answers   a USING (userid)
LEFT OUTER JOIN history   h USING (userid)
ORDER BY COALESCE(q.question_count, 0) +
         COALESCE(a.answer_count, 0) + 
         COALESCE(h.history_count, 0) DESC;

    The problem with your query is that you are not counting the answers, questions and history per user, but over all users instead. Then you cross join these totals to each user. You need GROUP BY to get counts per user and inner joins to joins users counts to their users.

    I assume here that all tables have a userid that we can use for the join. If this column is called differently in the tables, then you’ll have to adjust the query slightly, of course.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search