CODEWITHSUNDEEP
c#sql-server
Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34150

sql server: getting several records each with several sub records

I have 2 tables, first one contains posts, and the second one contains comments for posts

Posts
-------
[ID] int
{Text] nvarchar(max)
.... (some other columns...)

Comments
--------
[ID] int
[PostID] int
[Comment] Nvarchar(1024)
.... (some other columns)

I want to load top 20 posts and top 10 comments of each post. the problem is as I'm going to show posts on my page I need comments of each posts after the post.

Any Suggestions?

Upvotes: 0

Views: 97

Answers (2)

Steve
Steve

Reputation: 5545

This will help you get all the data you need in 1 trip. Your DataAdapter.Fill command should have something like this:

SELECT TOP 20 * FROM Posts WHERE ...

And then in the same command, seperated by a semicolon ;, have your second SQL which will result in a 2nd table being added in your DataSet.

SELECT * 
FROM 
    (SELECT 
         *, 
         ROW_NUMBER() OVER(PARTITION BY PostID ORDER BY ID desc) RowNum 
     FROM 
         Comments 
     WHERE 
         PostID IN (SELECT TOP 20 ID FROM Posts WHERE ...)
    ) tmp 
WHERE 
    RowNum <= 10

This SQL will get you the comments for all the posts you asked for above, but just the last 10 for each PostID.

Note that 

SELECT TOP 20 ID FROM Posts WHERE ...

should be the exact SQL from your first SQL statement except the single return field is the ID column.

After you fill the dataset, save it for later so when the record changes (selects Post), you can then get the data that is already local for your comments

Sorry about this being in VB

Dim DRs as Datarow() = {Your Dataset}.Tables(1).Select("PostID = " & {The current records ID})

Hope this helps. It should get you at least pointed in the right direction.

Upvotes: 1

Tarik
Tarik

Reputation: 11209

Use a dataset containing two datatables connected with a relation. You can achieve that through Visual Studio designer. You can also use the newer Entity Framework. Alternatively, you can select all the posts, then as you loop through the posts, issue an SQL statement to grab the comments from the DB (rather slow). You could also grab all the comments at once, then as you loop through the posts, have a nested loop filter out the corresponding comments. You could also use a LINQ query to group comments by post. This particular cat can bs skinned many many ways :-)

Upvotes: 0

Related Questions