accordingly:
select a.HikeName, a.HikeID, a.HikeDate, a.HikeStars, COUNT(b.hikeid)
from Hike A INNER JOIN
HikeReview B ON A.hikid = B.hikeID
group by a.hikename, a.hikeid, a.hikedate, a.hikestars
order by HikeDate Desc
John Munyan wrote:
> Hi, I have 2 tables - the hike table which contains all the per hike
> data. The second table is a user review table which is assoicated
> with the Hike table by HikeID.
>
>
> 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]
