Implementation issue with displaying data from seperate tables

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

Scenario 1:

"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

Scenario 2:

"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

Preferred Scenario:

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

This is a asp.net application, so ado.net will be used, and I'm using Sql Server 2008.

Any ideas

more ▼

asked Nov 27, 2009 at 12:02 AM in Default

ingig gravatar image

3 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

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'?

select  top50status.statusid,  top50status.text  comments.commentid,  comments.text from  (  select top 50 statusid, text from status --orderby something?????  ) top50status left join  comments on comments.statusid = top50status.statusid 
more ▼

answered Nov 27, 2009 at 05:20 AM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 28, 2009 at 05:25 PM

ingig 1 gravatar image

ingig 1

Please mark Kev's answer as accepted - that way anybody searching in the future with the same question will know that Kev's answer solved your issue.
Nov 28, 2009 at 07:35 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

In most cases using a join is the best and most efficient way to retrieve data like that.

more ▼

answered Nov 27, 2009 at 02:10 AM

TimothyAWiseman gravatar image

15.6k 21 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 27, 2009 at 12:02 AM

Seen: 1186 times

Last Updated: Nov 27, 2009 at 02:10 AM