[SQL] return a single row in a left join?

[growse]

I'm trying to do a SQL query on a table that holds a forum-like data structure. Each row has an ID, a timestamp, a parent ID and some content text. I'm trying to return a result set that lists all of the threads along with the timestamp and ID of the latest post in that thread.

If I do:

select * from chatitems ci
left join chatitems cr on ci.id=cr.parent_id
where ci.parent_id is null;

I obviously get multiple rows for each thread that has >1 reply - 1 row for each reply.

What I need is for each thread to have a single row and the columns to either be null if there's no reply, or the single row corresponding to the latest timestamp on the join if there is a reply.

If I do:

select ci.id,ci.timestamp,ci.body,max(cr.timestamp),cr.id from chatitems ci
left join chatitems cr on ci.id=cr.parent_id
where ci.parent_id is null
group by ci.id;

I get a single row as I need, but I only get the correct cr.timestamp. The cr.id returned doesn't correspond to the same row that the cr.timestamp is in.

Put another way, I need to fetch every row where parent_id is null to get all threads, and then for each thread I need to get all replies, ordered descending by timestamp, limited to the first result. Is this even possible?

Hopefully that makes some sort of sense. Database is MySQL.

 

[growse]

I figured it out. Didn't know you could left join to a separate query:

select ci.id,postmembers.name as postname,ci.body,ci.timestamp,postmembers.id as memberid, ci.deleted_timestamp, deletemembers.name as deletename,

cr.timestamp, cr.replies,replymembers.name

from chatitems  ci
left join (select count(*) as replies,max(timestamp) as timestamp,parent_id, id, member_id from chatitems where parent_id is not null group by parent_id) cr on cr.parent_id=ci.id
inner join members as postmembers on postmembers.id = ci.member_id
left join members as deletemembers on deletemembers.id = ci.deleted_id
left join members as replymembers on cr.member_id=replymembers.id
group by id
order by id asc