Implementation issue with displaying data from seperate tables

Question

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

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

Answer 1

0

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

answered Nov 27 '09 at 05:20 AM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

(comments are locked)

Answer 2

0

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 '09 at 05:25 PM

ingig 1
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 '09 at 07:35 PM Matt Whitfield ♦♦

(comments are locked)

Answer 3

0

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

answered Nov 27 '09 at 02:10 AM

TimothyAWiseman
14.3k ● 16 ● 20 ● 29

(comments are locked)

Implementation issue with displaying data from seperate tables

Follow this question

Related Questions