At any rate. I use a sql query to generate the left nav. It is similiar to
select HikeName, HikeID, HikeDate, HikeStars
from Hike
order by HikeDate Desc
I want to add the count of users reviews to the menu system so people can see what how many user reviews exist for each hike. This information is in the UserReviews table. As I said releated by HikeID.
If I try a join I will get back mulitple rows since there is invariably more UserReviews that 1. I want to bring back a count value.
It would seem like a sub query or something but I don't get how this would be crafted.
I would like the return to be the above query plus one field which is the count of reviews for each HikeId.
Thus-
Pinnacle Peak, HikeID 40, 6/11/03, 5, (count of records in UserReviews where Hike.HikeId = UserReviews.HikeId)
Is this possible? I would prefer this to be agregated in a sql statement since I am looping over all the records in the original query and would have to write a second query in the loop and query the UserReview table for the hike ID bringing back a the count. It would be possible but I am thinking there must be a quick, more elegant solution to this that probably someone more enlightened can just rattle off. Any ideas?
Thanks,
John
Http://www.attrition.ws
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
