Mysql get latest row of self referencing relationship

Question

I have a comments table and allow people to edit their comments. Instead of overwriting comments I create a new comment and associate it to its "parent". Additionally, I add the child information to the parent.

id, user_id, comment_id__parent, comment_id__child, comment, created_dt

SQL Fiddle here

Now my problem is that I would like to get all comments of a specific user but only the latest update of a comment.

This is giving me a huge headache for a while and I would appreciate very much your input!

Sorry that i wasn't more specific but that's the expected result set: http://sqlfiddle.com/#!2/2457e0/31 — n00b, May 14 '14 at 13:55

score 2 · Accepted Answer · answered May 14 '14 at 13:14

2

If your fiddle is correct, you should be able to do this:

SELECT * FROM comments 
 WHERE comment_id__child IS NULL AND user_id=1;

This works if you always populate the comment_id__child for 'parent' comment when editing it.

answered May 14 '14 at 13:14

vhu

12,244
11
38
48

are you forget Order By clause? – Ronak Shah May 14 '14 at 13:16
@RonakShah, `ORDER BY` isn't strictly necessary unless for returning the expected results. Of course it may be included, if needed. – vhu May 14 '14 at 13:19
he wants latest comments thats why you need to add order by created_dt desc – Ronak Shah May 14 '14 at 13:22
No, "I would like to get **all comments** of a specific user but only the **latest update**" hence the `ORDER BY` is not necessary. – vhu May 14 '14 at 13:31

score 0 · Answer 2 · answered May 14 '14 at 13:14

0

for uer_id =1

select * from comments where user_id=1 order by created_dt desc;

answered May 14 '14 at 13:14

Ronak Shah

1,539
2
13
20

score 0 · Answer 3 · answered May 14 '14 at 13:24

This works perfectly! You can see the result in your sql fiddle.

select * from comments group by user_id having count(id) = 1
UNION
select * from comments where user_id in (select user_id from comments group by user_id having count(id) > 1) and comment_id__child is null and comment_id__parent is not null;

score 0 · Answer 4 · answered May 14 '14 at 13:24

0

I think i found a solution:

SELECT *
FROM comments
WHERE user_id = 1
  AND ( (comment_id__parent IS NULL
         AND comment_id__child IS NULL)
       OR (comment_id__parent IS NOT NULL
           AND comment_id__child IS NULL) )
ORDER BY created_dt DESC

answered May 14 '14 at 13:24

n00b

16,088
21
56
72

This would only work for the user id 1. If you need to see the results for all users, you can check my comment. It works perfectly. – Kanishk Dudeja May 14 '14 at 13:32

Mysql get latest row of self referencing relationship

4 Answers4