0

I edited from my prior question...I was trying hard but I returned the results only by the main post id....Not using the last comment id (expected)

So I want to get from the bottom to the top. not viceversa.

Posts

id_post     |       post          | comment_of 
    25          main post               0 
    26          comment 1st level       25 
    27          comment 2nd level       26 
    28          another post            0 
    29          comment not related     14 
    30          comment 3rd level       27 
    31          comment not related     13

Query 

    SELECT id_post, 
           post, 
           comment_of 
    FROM (SELECT * FROM posts 
    ORDER BY comment_of, id_post) posts_sorted, 
            (SELECT @pv := '25') initialisation 
    WHERE find_in_set(comment_of, @pv) 
    AND length(@pv := concat(@pv, ',', id_post)) 
    OR id_post=@pv

Result 

id_post     |       post          | comment_of 
    25          main post               0 
    26          comment 1st level       25 
    27          comment 2nd level       26 
    30          comment 3rd level       27

working fiddle http://sqlfiddle.com/#!9/c07667/2

I want to get the results by the last comment, Not by the main post....in this case I want to get the same result by choosing 30 (last comment). Not vice versa

joe
  • 219
  • 2
  • 11
  • MySQL has basically no support for recursive or hierarchical queries. You can do this using a loop in a stored procedure. – Gordon Linoff Mar 19 '18 at 02:08
  • what about `Repeated Self-joins`? – joe Mar 19 '18 at 02:11
  • @TimBiegeleisen I already updated the question and added a working fiddle. ablove is what I expect...need some modifications. if able to help thank you. – joe Mar 19 '18 at 18:26
  • @GordonLinoff I updated the question....I expect to return the results choosing the id of the last comment...to get to the top. – joe Mar 19 '18 at 18:26

0 Answers0