Ah. But by "loop through results and fetch comments" I thought you meant something like query then loop and do a query for each user...
What I wrote would be perfectly acceptable for me though. ;) > That would do it but not from sql. I know really don't like having to use > cfoutput to group I wish they would add group to cfloop at least then it > would be a little easier. > > Tyler Clendenin > GSL Solutions > > -----Original Message----- > From: Tony Schreiber [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 06, 2003 7:46 AM > To: CF-Talk > Subject: RE: SQL: Brain Teaser > > > > USERS TABLE > > > ----------------- > > > USER_ID > > > USERNAME > > > etc... > > > > > > COMMENTS TABLE > > > ------------------------- > > > COMMENT_ID > > > USER_ID > > > COMMENT > > > etc... > > > > > > I know I can query USERS and then loop through the results fetching > > > the comments... but I'd rather not do that. Here's the results I'm > > > trying to get... in a SINGLE query if possible: > > > > > > USER_ID,USERNAME,MERGED_COMMENTS > > > 1,joe,comment1<br>comment2<br>comment3 > > > 2,sam,comment > > > 3,sue,comment<br>another comment<br>etc. > > <CFQUERY NAME="mergedcomments"> > SELECT username, comment > FROM users u, comments c > WHERE u.user_id = c.userid > ORDER BY username > </CFQUERY> > > <CFOUTPUT QUERY="mergedcomments" GROUP="username"> > #username#,<CFOUTPUT>#comment#<BR></CFOUTPUT> > </CFOUTPUT> > > Wouldn't that do it? > > Tony Schreiber, Senior Partner > Man and Machine, Limited > mailto:[EMAIL PROTECTED] http://www.technocraft.com > > http://www.is300.net The Enthusiast's Home of the Lexus IS300 since 1999 > ***** PARTS STORE NOW OPEN ***** http://www.is300.net/store/ > http://www.simplemessageboard.com Free Forum Software for Cold Fusion > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
