CODEWITHSUNDEEP
sqlsql-serverjoinforeign-keys
James W Taylor II
James W Taylor II

Reputation: 11

SQL Server Query - Retrieve list of articles and multiple comments per article for preview in single query?

... like Facebook's comments preview when looking at a list of posts on someone's wall or in your news feed.

My database is currently set up with a table articles with a primary key articleID.

I have another table for comments with the primary key commentID and a foreign key articleID to the appropriate article.

When I'm retrieving one article with the comments for that article, this is trivial and doesn't burden me at all. However, is it possible to write a single query that will join these two tables together and retrieve a list of say the most recent 10 articles (to build a preview from) and say 3 comments per articles (to create a comments preview)?

I can't figure this out and would love an experienced opinion. Cheers and thanks!

I don't have the reputation to answer my own question, so instead I'll just post the answer here. Thanks to both commentators so far.

Wow, it's shocking how I thought I understood joins. The simple fact of the matter is I did not. I thought that left joining two tables in a one-to-many relationship could only return one row per row in the left table. In retrospect, that is obviously wrong as which right row would be returned? Upon considering this problem in view of the two current responses, I found these two articles using ideas those comments led me to. As such, thanks for your help.

How can a LEFT OUTER JOIN return more records than exist in the left table?

Select first record in a One-to-Many relation using left join

To thoroughly answer the question for anyone that stumbles upon this with such a neophyte question...

Articles table:

ArticleID----------ArticleName
100                  Art 1
200                  Art 2

Comments table:

CommentID----------ArticleID-------Comments------Date
1                     100           test         date 1
2                     100           test2        date 2
3                     100           test3        date 3
4                     100           test4        date 4

The second article cited above is what really solved my problem. In my case, I really need something like this:

SELECT 
TOP 10 articles.*,
       comments.*
  FROM articles
  LEFT
  JOIN comments
    ON articles.articleID = comments.articleID
   AND comments.commentID =
        ( SELECT TOP 3
                 commentID
            FROM comments
           WHERE comments.articleID = articles.articleID
       )
;

Upvotes: 1

Views: 921

Answers (2)

Vamshi Krishna
Vamshi Krishna

Reputation: 33

Consider your Article table structure below

ArticleID----------ArticleName
100                  Art 1
200                  Art 2

consider your Comments table structure below

CommentID----------ArticleID-------Comments------Date
1                     100           test         date 1
2                     100           test2        date 2
3                     100           test3        date 3
4                     100           test4        date 4

Try this query

SELECT TOP 3 A.ARTICLEID, C.COMMENTS FROM ARTICLE A
LEFT JOIN COMMENTS C ON C.ARTICLEID = A.ARTICLEID WHERE A.ARTICLEID = 100
ORDER BY C.DATE DESC

If this is not what you are looking for, Consider improving your question.

Happy SQLing....

Upvotes: 0

rhealitycheck
rhealitycheck

Reputation: 660

well a simple query to join the two together would be

select top 10 a.articleid, c.commentid, <other fields>
from article a
left join comments c on a.articleid = c.articleid
order by <date> desc

i'm using a left join so that you will still return records that do not have any comments. obviously you need to do some more work to the query to actually have it return the values you want but this should be a base that you can use.

if you had more information in your example like sample table information that could help us give you more feedback like how to return the top 10.

Upvotes: 1

Related Questions