Think about how the main Facebook page is filled with statuses from your friends and comments about those statuses
We now have a reference of what we want, we'll setup the table schema in a simple way
STATUS statusId text COMMENTS commentId statusId text
as you can see each status can have multiple comments,
The question is: What is the best way to retrieve this data from the database?
I see two scenarios where I retrieve the top 50 rows
"SELECT TOP 50 * FROM status"
and when going through each datarow do
"SELECT * FROM comments WHERE statusId=@statusId"
This will create 51 queries to the database, which I don't like. Need to think about load on the server
"SELECT TOP 500 * FROM status OUTER JOIN comments ON status.statusId=comments.statusId"
This is just one query, but now I need to hope that one status doesn't have 500 comments
I was thinking of something like this:
"SELECT TOP 50 *, (select * from comments where statusId=status.statusId) AS commentTable FROM status"
where commentTable is a table variable that is returned with the results, I know this isn't possible but it's kinda what I'm thinking