CODEWITHSUNDEEP
mysqlsqldatabase
pepe
pepe

Reputation: 9909

Merging 2 MySQL queries and ordering results by timestamp

I have a query that returns all stream posts from users (with comments):

SELECT  *
FROM   (SELECT *
       FROM stream_ps
       ORDER BY stream_ps_timestamp DESC
       LIMIT $offset, $limit) sps
        JOIN user_profiles
          ON user_id = sps.stream_ps_author_id
        LEFT JOIN (SELECT stream_cm_id,
                          stream_cm_author_id,
                          stream_id_fk,
                          stream_ps_id_fk,
                          stream_cm_text,
                          stream_cm_timestamp,
                          first_name as comm_first_name,
                          last_name as comm_last_name,
                          facebook_id as comm_fb_id,
                          picture as comm_picture
                    FROM  stream_cm
                    JOIN  user_profiles
                      ON  user_id = stream_cm_author_id) AS c
          ON sps.stream_ps_id = c.stream_ps_id_fk
ORDER   BY sps.stream_ps_id DESC, c.stream_cm_id ASC

and another query that returns all forum posts:

SELECT  qa.*,
        user_profiles.*,
        n.pid,
       Ifnull(n.ans_count, 0) AS ans_count
FROM  (SELECT * FROM forum_qa
       ORDER BY forum_qa_type,forum_qa_timestamp DESC
       LIMIT $offset, $limit) qa
       LEFT JOIN user_profiles
         ON user_id = qa.forum_qa_author_id
       LEFT JOIN (SELECT forum_qa_parent_id AS pid,
                         COUNT(*)           AS ans_count
                  FROM   forum_qa
                  WHERE  forum_qa_parent_id IS NOT NULL
                  GROUP  BY forum_qa_parent_id) AS n
         ON qa.forum_qa_id = n.pid
WHERE  qa.forum_qa_type = 1
ORDER  BY qa.forum_qa_timestamp DESC

I would like to merge both into a single query and reorder the results so that they are reverse chronological (ie, timestamp DESC, with most recent showing first).

So the end result would show a thread of the most recent entries, regardless if post or forum.

Is there a way of doing this?

Upvotes: 1

Views: 1231

Answers (3)

Bort
Bort

Reputation: 7618

Since the tables have different fields, if you really need to do the sorting in SQL, you could union the two result sets into one big table which has the fields for both. Something along the lines of

SELECT * FROM (
    SELECT col1, col2, null, null, time_stamp FROM table1
    UNION
    SELECT null, null, colA, colB, time_stamp FROM table2
) entries
ORDER BY time_stamp DESC
LIMIT $offset, $limit

Upvotes: 1

Umbrella
Umbrella

Reputation: 4788

It sounds like you want UNION. To use UNION, you'd need to make sure the columns match up; from the docs

UNION is used to combine the result from multiple SELECT statements into a single result set.

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type.

Basic usage is like this

SELECT id, value FROM tableA
UNION
SELECT id, value FROM tableB
ORDER BY id

So, if your two queries have field lists that match in type and order, you can put them together pretty simply.

Upvotes: 0

Travesty3
Travesty3

Reputation: 14469

I think you're looking for a UNION.

Upvotes: 0

Related Questions