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
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
and when going through each datarow do
This will create 51 queries to the database, which I don't like. Need to think about load on the server
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:
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
If I understand correctly, you want the top 50 status with all their comments?
You can do this using a join and an in-line view or derived table. My only concern with your data is that there seems to be no particular ordering on the status, so how can you define the 'top'?
Brilliant, the answer works perfectly.
The table schema was very simplified, missing things like timestaps, userid and probably something else. I'll probably order by StatusId desc
answered Nov 28, 2009 at 05:25 PM
In most cases using a join is the best and most efficient way to retrieve data like that.
answered Nov 27, 2009 at 02:10 AM