Sort MySQL select from another table data

Question

I have 2 tables that involve this query.

Table : Discussions            Table : Comments
--------------------           -------------------------------------
|id | content      |           | id | d_id | content   | timestamp |
--------------------           -------------------------------------
| 1 | Text String  |           | 1  |  2   | Comment 1 | timestamp |
| 2 | Text String  |           | 2  |  3   | Comment 2 | timestamp |
| 3 | Text String  |           | 3  |  4   | Comment 3 | timestamp |
| 4 | Text String  |           | 4  |  2   | Comment 4 | timestamp |
| 5 | Text String  |           | 5  |  3   | Comment 5 | timestamp |
--------------------           -------------------------------------

Now I need to get the id from the discussion table, and I need to sort it using the d_id in the comment table according to a Unix timestamp in the comments table. If there is no comment then I need to sort discussions using timestamp in the discussions table. so newly posted discussions without any comments will added to the top of the page.

Answer 1

Assuming you want to list just the content from Discussions ordered by timestamp from Comments, then you need to left join to include all discussions (including those without comments):

SELECT DISTINCT Discussions.id, Discussions.content
FROM Discussions
LEFT JOIN Comments ON Discussions.id = Comments.d_id
ORDER BY -Comments.timestamp DESC, Discussions.timestamp DESC

where I've used this answer to make sure the Discussions with null for timestamp, that is, the discussions with no comments, come last.

Answer 2

supposing that the connection between the two tables is

comments.d_id = Discussions.id

I would do it like this

select Discussions.*, comments.*
from Discussions 
left join comments
on comments.d_id = Discussions.id
order by comments.timestamp desc, comments.d_id

Answer 3

Let's take this in steps:

SELECT d.id,
       ( SELECT id FROM Comments
             WHERE discussion_id = d.id
             ORDER BY created_on DESC
             LIMIT 1 ) AS latest_comment_id
    FROM Discussions AS d
    WHERE d.is_active = 1
      AND d.is_pinned = 0
      AND d.is_sink = 0
    ORDER BY
        c.id IS NOT NULL,  -- to get non-commented discussions first
        d.timestamp  DESC, -- if you want latest first
    LIMIT 50;

And have

INDEX(discussion_id, created_on)

That should give you one row per discussion, with the comment-less discussions first (newest first), then those with comments (again, newest first).

Add more columns if you need more stuff from Discussions.

The second step would be get more info from Comments, if you desire. But first, let me know what you need from it. In particular, a COUNT(*) would be lead down a different path than fetching other columns.