Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DataBound
Starting Member
2 Posts |
 Posted - 2004-02-01 : 01:16:32
|
Hi everybody. What a great site this is. I have a query problem that I just can't figure out. My SQL is a little rusty so please bare with me.  I'm building a web application that will publish instructional tutorials. Each tutorial article will have a means for readers to leave feedback. My table structure is this.ARTICLESID          | PK - AutonumberTitle       | varcharDate        | datetimeetc, etc ...FEEDBACKPost_No    | PK - AutonumberID         | FK from Articles tableDate       | datetimeetc, etc ...In my query I am selecting the most recently written articles like this.SELECT TOP 3 ARTICLES.* FROM ARTICLES ORDER BY ARTICLES.[DATE] DESCI want to be able to retrieve the total number of feedback posts for each separate article. For example, if Article number 1 has 10 posts then I want to return that as part of the resultant query. I'm stumped on this one. Everything I try with Joins and the COUNT function just isn't working. How do I go about doing this? |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-02-01 : 01:24:39
|
| HiYou can join the feedback table and do a "group by" and count.Something like :SELECT TOP 3 ARTICLES.ID, ARTICLES.Title, ARTICLES.Date , Count(*) as NumberOfCommentsFROM ARTICLES INNER JOIN FEEDBACK ON FEEDBACK.ID = ARTICLES.IDGROUP BY ARTICLES.ID, ARTICLES.Title, ARTICLES.DateORDER BY ARTICLES.[DATE] DESCDamian |
 |
|
|
DataBound
Starting Member
2 Posts |
Posted - 2004-02-01 : 01:32:02
|
Cheers Damian!I came very close to that several times but wasn't quite able to hit the nail on the head so to speak.  Thanks again. |
|
|
|
|
|
|
Slightly tricky query.