Also what DB are you using, the get 5 latest part will be DB specific.. in Oracle, we can get the rownum. Something like this...
select * from ( select p.post, count(pc.*), rownum as rn from posts p right outer join post_comments pc on pc.postid = post.postid group by p.post ) where rn <= 5 On 3/19/07, Nathan Chen <[EMAIL PROTECTED]> wrote: > Joel, > > Are you trying to do a count on a particular post? If so, you might be > looking for this: > > Select count(*) from PostComments > where PostCommentsPostID = [your value] > > If you try to use the exact value of a certain Post rather than a ID, > you have to join Posts table. > > This may or may not what you want but the best way is to give us what > your current query looks like and what you are trying to achieve. > > Nathan Chen > > -----Original Message----- > From: Joel Watson [mailto:[EMAIL PROTECTED] > Sent: Monday, March 19, 2007 9:41 AM > To: CF-Newbie > Subject: Help with Query > > I have two tables, Posts and PostComments, the latter of which has a > foreign key on the PostID of Posts. > > On my output page, I am using an output query to display the latest 5 > Posts. Inside of this output query, I need to show how many comments > are related to the particular posts. > > I have tried a million combinations of joins and attempts at some loops, > but I cannot figure this out. All that I have been able to manage so > far is to either show the same number for each post, or to show a > descending number of all totals for each post. Moreover, my brain is > nearly fried from frustration. > > This seems like it should be incredibly simple, but I am at a complete > loss. > > Anyone have any ideas? > > Thanks! > > Joel > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Newbie/message.cfm/messageid:2640 Subscription: http://www.houseoffusion.com/groups/CF-Newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
