Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- news (
- id,
- subject,
- body,
- posted
- )
- comments (
- id,
- parent, // points to news.id
- message,
- name,
- posted
- )
- SELECT news.*, comments.name, comments.posted, (SELECT count(id) FROM comments WHERE comments.parent = news.id) AS numComments
- FROM news
- LEFT JOIN comments
- ON news.id = comments.parent
- AND comments.id = (SELECT max(id) FROM comments WHERE parent = news.id)
- select c.id, c.parent, c.message, c.name, c.posted
- c.message, c.name,
- c.posted -- same as comment_latest.recent
- from comments c
- join
- (
- select parent, max(posted) as recent
- from comments
- group by parent
- ) as comment_latest
- on c.parent = comment_latest.parent
- and c.posted = comment_latest.recent
- select
- n.id as news_id, n.subject, n.body, n.posted as news_posted_date
- c.id as comment_id,
- c.message, c.name as commenter_name, c.posted as comment_posted_date
- from comments c
- join
- (
- select r.parent, max(r.posted) as recent
- from comments r
- join
- (
- select id from news order by id desc limit $last_x_news
- ) news l
- on r.parent = l.id
- group by r.parent
- ) as comment_latest
- on c.parent = comment_latest.parent
- and c.posted = comment_latest.recent
- join news n on c.parent = n.id
- select
- id,
- subject,
- body,
- posted as news_posted_date,
- (select id from comments where parent = news.id order by posted desc limit 1) as comment_id,
- (select message from comments where parent = news.id order by posted desc limit 1) as message,
- (select name from comments where parent = news.id order by posted desc limit 1) as name,
- (select posted from comments where parent = news.id order by posted desc limit 1) as comment_posted_date,
- from news
- SELECT news.subject, news.body, comments.name, comments.posted
- FROM news
- INNER JOIN comments ON
- (comments.parent = news.id)
- WHERE comments.parent = news.id
- AND comments.id = (SELECT MAX(id)
- FROM comments
- WHERE parent = news.id)
- ORDER BY news.id
- 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 )
- SELECT MAX(id), parent
- FROM comments
- GROUP BY parent
- SELECT news.*, comments.*
- FROM news
- INNER JOIN recent_comments
- ON news.id = recent_comments.parent
- INNER JOIN comments
- ON comments.id = recent_comments.id
Add Comment
Please, Sign In to add comment