Am trying to create a message and commenting system (like facebook wall).
I have two tables
messages
--------
mid creator_id message created_date
----------------------------------------------------
1 3 msg one date
2 4 msg two date
3 8 msg three date
4 3 msg four date
comments
---------
cid mid creator_id comment created_date
--------------------------------------------------------------------
1 1 7 comment for msg one date
2 8 32 comment for msg three date
3 1 14 comment for msg one date
4 4 23 comment for msg four date
My requirement is that,
I want to display the five messages with latest three comments for each message. The message which got the latest comment should be in the first order (same as facebook wall).
Can I access this with a single query. Is my db structure is good? Am using mysql 5
Thanks in advance
Arun