Mysql How can I get 2 result sets in one query from one table?

Question

I have a mysql comment table like this.

+------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | userid | int(11) | NO | | 0 | | | comment | char(255) | NO | | NULL | | | content | int(11) | NO | MUL | 0 | | | ratings | int(11) | NO | | 0 | | | datetime | datetime | NO | | NULL | | | ip | int(10) unsigned | NO | | NULL | | | is_updated | tinyint(2) | NO | | 0 | | | record_num | int(11) | NO | PRI | NULL | auto_increment | +------------+------------------+------+-----+---------+----------------+ 

now I can get comments from this and usernames from another table using an INNER JOIN query like this.

I can get 3 top comments ORDER BY comments.ratings DESC

 SELECT comments.userid, users.username, comments.comment, comments.ratings, comments.datetime, comments.record_num , content.uploader , content.anonymous FROM comments LEFT JOIN users ON comments.userid = users.record_num LEFT JOIN content ON comments.content = content.record_num WHERE comments.content = ? ORDER BY comments.ratings DESC limit 3 

and

regular comments ORDER BY comments.datetime DESC.

 SELECT comments.userid, users.username, comments.comment, comments.ratings, comments.datetime, comments.record_num , content.uploader , content.anonymous FROM comments LEFT JOIN users ON comments.userid = users.record_num LEFT JOIN content ON comments.content = content.record_num WHERE comments.content = ? ORDER BY comments.datetime DESC limit ?, ? 

what I'm trying to do is show users the first 3 comments by their ratings first, and then the regular order of comments by comments. datetime DESC.

Now, how can I join two mysql queries in one?

Answer 1

As the other answer says - you can combine the results with union - it just means combining the two results together. It is important to note, however, that you cannot simply union those two queries together directly, since they use order by , so we need to wrap them in external queries and use a ranking variable so that we can restore the union in the order we want :

 select * from ( SELECT 1 as tbl, comments.userid, users.username, comments.comment, comments.ratings, comments.datetime, comments.record_num, content.uploader, content.anonymous, @rank := @rank + 1 as rank FROM comments LEFT JOIN users ON comments.userid = users.record_num LEFT JOIN content ON comments.content = content.record_num CROSS JOIN (select @rank := 0 ) q WHERE comments.content = ? ORDER BY comments.ratings DESC limit 3 ) q1 UNION ALL select * from ( SELECT 2 as tbl, comments.userid, users.username, comments.comment, comments.ratings, comments.datetime, comments.record_num, content.uploader, content.anonymous, @rank := @rank + 1 as rank FROM comments LEFT JOIN users ON comments.userid = users.record_num LEFT JOIN content ON comments.content = content.record_num CROSS JOIN (select @rank := 0 ) q WHERE comments.content = ? ORDER BY comments.datetime DESC limit ?, ? ) q2 ORDER BY tbl asc, rank asc; 

union by default distinct means that it will not duplicate the rows found in both result sets, however there is also no guarantee that the rows will be returned in the expected order, therefore, it is necessary to mark each table with its own tbl value, and then order by this field.

If you were sure that there would be no duplicates, you can eliminate the re-check by using union all instead of union

Answer 2

To answer your question, yes, you can combine your queries with UNION , but

what I'm trying to do is show users the first 3 comments, first the ratings, and then the regular comments on the comments. DESC

If you are trying to achieve something like the functionality of stackoverflow comments, where the top comments (or comments with upvotes) are displayed first, then I think the individual queries are good enough .

If your request is combined, then the user needs to download all the top and regular comments . Also, it is somehow difficult to make or implement pagination with a large query that has UNION (based on my experience, because im is still noob when encoding)

Disclamer: HARD, but not impossible

If I'm the one who is going to compose this problem, first my program will load the top 3 comments, after which the "Show more comments" button will appear, which will be sent to my server using AJAX and add the result (regular comments) to the list of comments shown. In this case, the requests should be separate, as you have now.

Answer 3

To stack result sets on top of each other in one set, you need to use "UNION" - https://dev.mysql.com/doc/refman/5.0/en/union.html