code_junkie

Get the latest row from another table in MySQL

Nov 13th, 2011
155
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.29 KB | None | 0 0
  1. news (
  2. id,
  3. subject,
  4. body,
  5. posted
  6. )
  7.  
  8. comments (
  9. id,
  10. parent, // points to news.id
  11. message,
  12. name,
  13. posted
  14. )
  15.  
  16. SELECT news.*, comments.name, comments.posted, (SELECT count(id) FROM comments WHERE comments.parent = news.id) AS numComments
  17. FROM news
  18. LEFT JOIN comments
  19. ON news.id = comments.parent
  20. AND comments.id = (SELECT max(id) FROM comments WHERE parent = news.id)
  21.  
  22. select c.id, c.parent, c.message, c.name, c.posted
  23.  
  24. c.message, c.name,
  25.  
  26. c.posted -- same as comment_latest.recent
  27.  
  28. from comments c
  29. join
  30. (
  31. select parent, max(posted) as recent
  32. from comments
  33. group by parent
  34. ) as comment_latest
  35. on c.parent = comment_latest.parent
  36. and c.posted = comment_latest.recent
  37.  
  38. select
  39.  
  40. n.id as news_id, n.subject, n.body, n.posted as news_posted_date
  41.  
  42. c.id as comment_id,
  43. c.message, c.name as commenter_name, c.posted as comment_posted_date
  44.  
  45. from comments c
  46. join
  47. (
  48. select r.parent, max(r.posted) as recent
  49. from comments r
  50. join
  51. (
  52. select id from news order by id desc limit $last_x_news
  53. ) news l
  54. on r.parent = l.id
  55. group by r.parent
  56. ) as comment_latest
  57. on c.parent = comment_latest.parent
  58. and c.posted = comment_latest.recent
  59.  
  60. join news n on c.parent = n.id
  61.  
  62. select
  63. id,
  64. subject,
  65. body,
  66. posted as news_posted_date,
  67. (select id from comments where parent = news.id order by posted desc limit 1) as comment_id,
  68. (select message from comments where parent = news.id order by posted desc limit 1) as message,
  69. (select name from comments where parent = news.id order by posted desc limit 1) as name,
  70. (select posted from comments where parent = news.id order by posted desc limit 1) as comment_posted_date,
  71. from news
  72.  
  73. SELECT news.subject, news.body, comments.name, comments.posted
  74. FROM news
  75. INNER JOIN comments ON
  76. (comments.parent = news.id)
  77. WHERE comments.parent = news.id
  78. AND comments.id = (SELECT MAX(id)
  79. FROM comments
  80. WHERE parent = news.id)
  81. ORDER BY news.id
  82.  
  83. SELECT news.*, comments.name, comments.posted FROM news INNER JOIN comments ON news.id = comments.parent WHERE comments.id = (SELECT max(id) FROM comments WHERE parent = news.id )
  84.  
  85. SELECT MAX(id), parent
  86. FROM comments
  87. GROUP BY parent
  88.  
  89. SELECT news.*, comments.*
  90. FROM news
  91. INNER JOIN recent_comments
  92. ON news.id = recent_comments.parent
  93. INNER JOIN comments
  94. ON comments.id = recent_comments.id
Add Comment
Please, Sign In to add comment