-1

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.

  • Add the expected result set to fully understand what you want exactly – Yassine LAADIDI Mar 2 '17 at 15:15
  • I expect there is a JOIN and an ORDER BY clause in the query you tried, right? – mustaccio Mar 2 '17 at 16:11
  • yes ... there is INNER JOIN & ORDER BY :) – Shyamin Ayesh Mar 2 '17 at 16:20
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.

  • I just need the id of the discussion ordered by timestamp from associated comments with that discussion. I also need the latest discussion if discussion don't have any comments. – Shyamin Ayesh Mar 2 '17 at 16:22
  • @ShyaminAyesh: you changed the goal posts. It's completely unfair to ask a question and then comment that the answer doesn't do quite something you never specified. All query requirements need to be listed in your question. – Jacobm001 Mar 2 '17 at 16:27
  • I changed the question for better readability :D sorry for the issue & please take a look it now. :) – Shyamin Ayesh Mar 2 '17 at 16:41
  • @ShyaminAyesh Your description of the Discussions table still doesn't include a timestamp field? Is one to assume that the order of the id implies which is oldest? – wogsland Mar 2 '17 at 16:44
  • @wogsland : assume that the order of discussions.timestatmp implies which is oldest. – Shyamin Ayesh Mar 2 '17 at 17:03
0

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
  • this will won't work if there is no comments associated with discussions. – Shyamin Ayesh Mar 2 '17 at 15:42
0

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.

  • This is nearly perfect. But this also returns the same Discussions row more than 1 time. – Shyamin Ayesh Mar 3 '17 at 3:21
  • 1
    And which Comment should be returned if there are multiple ones? If you want the "latest", then you are into the realm of Groupwise Max. – Rick James Mar 3 '17 at 3:55
  • yes. if there is multiple comments for single discussion I need the latest one :D – Shyamin Ayesh Mar 3 '17 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.