SQL, SELECT from two tables without getting all rows

Question

I have two tables: COMMENT and COMMENTHISTORY

Now I need to select cells from both tables, for example:

 SELECT c.Id, c.Userid, ch.Text, ch.Timestamp FROM COMMENT c, COMMENTHISTORY ch WHERE ch.CommentId = c.Id ORDER BY ch.Timestamp DESC 

It works great. The only problem is that COMMENTHISTORY has several lines for each COMMENT , so SELECT ends up getting several lines for each comment.

I need to get one line for each comment. A line where ch.Text and ch.Timestamp correspond to the last corresponding COMMENTHISTORY line.

Any ideas on how to do this?

Thanks.

Answer 1

Query:

 SELECT c.Id, c.Userid, ch.Text, ch.Timestamp FROM COMMENT c INNER JOIN COMMENTHISTORY ch ON ch.CommentId = c.Id WHERE ch.Timestamp = (SELECT MAX(ch2.Timestamp) FROM COMMENTHISTORY ch2 WHERE ch2.CommentId = c.Id ) ORDER BY ch.Timestamp DESC 

Answer 2

This will fix the problem.

 SELECT c.Id, c.Userid, ch.Text, ch.Timestamp FROM COMMENT c LEFT JOIN COMMENTHISTORY ch on C.ID = ch.comment_id WHERE ch.CommentId = c.Id GROUP BY c.id ORDER BY ch.Timestamp DESC 

Answer 3

 SELECT c.Id, c.Userid, ch.Text, max(ch.Timestamp) FROM COMMENT c, COMMENTHISTORY ch WHERE ch.CommentId = c.Id GROUP BY C.ID, c.userid, ch.text 

Answer 4

This may help you:

 SELECT c.Id, c.Userid, ch.Text, ch.Timestamp FROM COMMENT c LEFT JOIN COMMENTHISTORY ch ON ch.CommentId = c.Id ORDER BY ch.Timestamp DESC 

Answer 5

Use this:

 SELECT c.Id, c.Userid, Max(ch.Text), max(ch.Timestamp) FROM COMMENT c, COMMENTHISTORY ch WHERE ch.CommentId = c.Id GROUP BY C.ID, c.Userid ORDER BY ch.Timestamp DESC 

Answer 6

  SELECT c.Id, c.Userid, max(ch.Timestamp),max(ch.text) FROM COMMENT c, COMMENTHISTORY ch WHERE ch.CommentId = c.Id Group By c.Id, c.Userid ORDER BY ch.Timestamp DESC 

Answer 7

This query will select the last CommentHisotry entry for the last entry for this comment identifier:

 WITH LastComment AS ( SELECT CH.CommentId, MAX(CH.Timestamp) as Date FROM COMMENTHISTORY CH GROUP BY CH.CommentId ) SELECT c.Id, c.Userid, ch.Text, ch.Timestamp FROM COMMENT c INNER JOIN LastComment lc ON LC.CommentId = c.Id INNER JOIN COMMENTHISTORY ch ON ch.CommentId = lc.CommentId and ch.TimeStamp = lc.Date ORDER BY ch.Timestamp DESC 

Answer 8

Try using an inner join and put your conditions in the Where clause. For more information, please take a look at this: Combines the selection chart

Answer 9

That should do it ...

 ;WITH CTE AS ( SELECT ch.CommentId, ch.Text, ch.TimeStamp, ROW_NUMBER() OVER (PARTITION BY ch.CommentId ORDER BY ch.TimeStamp DESC) RowNum FROM COMMENTHISTORY ch ) SELECT c.Id, c.Userid, CTE.Text, CTE.TimeStamp FROM COMMENT c INNER JOIN CTE ON c.Id = CTE.CommentId WHERE RowNum = 1