CODEWITHSUNDEEP
mysql
Christoffer
Christoffer

Reputation: 492

Merging and sorting two tables

I have two tables.

"threads" with the columns ID, forumID, title, content, unixtime

"comments" with the columns ID, threadID, content, unixtime

I would like to do a mysql query which sorts all the rows within these two tables based on their unixtime, but also not showing the same "threadID" twice.

The following query will merge the two tables and sort them, but I cant remove duplicates of the threads. I've tried with some GROUP BY and DISTINCT, but with no luck.

(SELECT unixtime, ID, threadID FROM comments)
UNION
(SELECT unixtime, ID, forumID FROM threads)
ORDER BY unixtime DESC

Its for showing of the frontpage of a forum, therefor it would make no sense to let the same thread appear more than once.

EDIT: I want to show both the newest comments and the newest threads.

Upvotes: 0

Views: 295

Answers (3)

J A
J A

Reputation: 1766

Easy solution if you don't have keys to join:

   Select DISTINCT tbl.unixtime from
    (
    (SELECT unixtime, ID, threadID FROM comments)
    UNION
    (SELECT unixtime, ID, forumID FROM threads)
    ORDER BY unixtime DESC))
    As tbl

Upvotes: 0

Robin Castlin
Robin Castlin

Reputation: 10996

SELECT unixtime, ID, type, type_id
FROM (( SELECT unixtime, ID, 'comment' AS type, threadID, forumID
        FROM (  SELECT unixtime, ID, threadID
                FROM comments
                ORDER BY unixtime DESC) AS h
        GROUP BY threadID)
        UNION ALL
      ( SELECT unixtime, ID, 'thread', ID, forumID
        FROM threads)
        ORDER BY unixtime DESC) AS h2
GROUP BY threadID
ORDER BY unixtime DESC

I changed some stuff. I asume you want to know if it's a forum or thread id?

Upvotes: 1

Uttara
Uttara

Reputation: 2534

This will help you to get only unique threadID

(SELECT forumID, ID, unixtime FROM threads)
UNION
(SELECT distinct(threadID), ID, max(unixtime) FROM comments GROUPBY threadID)
ORDER BY unixtime DESC

Upvotes: 0

Related Questions