well i think you might just need to add ORDER BY journoid, region, town to your query.
Duncan Cumming IT Manager http://www.alienationdesign.co.uk mailto:[EMAIL PROTECTED] Tel: 0141 575 9700 Fax: 0141 575 9600 Creative solutions in a technical world ---------------------------------------------------------------------- Get your domain names online from: http://www.alienationdomains.co.uk Reseller options available! ---------------------------------------------------------------------- ---------------------------------------------------------------------- Dave Phipps <dave@phipps- To: <[EMAIL PROTECTED]> cf.co.uk> cc: Subject: Re: [ cf-dev ] grouping output problem 01/23/03 03:52 PM Please respond to dev The expected output should look like: Release Area Coverage: Region: Buckinghamshire Oxfordshire Town: Chipping Norton Oxford I am trying to get the details of regions, towns for a particular journalist as defined by the journoid. If the journalist has more than one region and/or town then the query outputs as many rows as there are regions + towns. So in my example the journalist has regions of Bucks and Oxon and towns of Chipping Norton and Oxford result in a recordcount of 4. I am then trying to group the results so that I don't get the regions or towns duplicated. Thanks Dave At 15:38 1/23/2003 +0000, you wrote: >I'm not entirely sure what you're trying to do. what should the expected >output look like? AFAIK, to use the Group attribute in CFOutput properly, >you need to have ordered by that field(s) in your query. > > >Duncan Cumming >IT Manager > >http://www.alienationdesign.co.uk >mailto:[EMAIL PROTECTED] >Tel: 0141 575 9700 >Fax: 0141 575 9600 > >Creative solutions in a technical world > >---------------------------------------------------------------------- >Get your domain names online from: >http://www.alienationdomains.co.uk >Reseller options available! >---------------------------------------------------------------------- >---------------------------------------------------------------------- > > > > > Dave > Phipps > > <dave@phipps- To: > <[EMAIL PROTECTED]> > cf.co.uk> cc: > > Subject: Re: [ cf-dev ] > grouping output problem > 01/23/03 > > 03:35 > PM > > Please > > respond > to > > dev > > > > > > > > >The query is as follows: > ><cfquery name="journoDetails" datasource="#request.dsn#" dbtype="ODBC"> > SELECT journoDetails.journoid, journoDetails.journoName, >journoDetails.journoTel, journoDetails.journoEmail, >journoDetails.journoMediaTitle, journoDetails.journoPword, >journoDetails.isNational, region.regionid, region.region, towns.townid, >towns.town, journoRoles.role, journoRoles.journoRolesid, >natOption.natOptid, natOption.natOpt >FROM journoDetails LEFT JOIN journoRegion ON journoDetails.journoid = >journoRegion.journoid > LEFT JOIN region ON journoRegion.regionid = region.regionid > LEFT JOIN journoTown ON journoDetails.journoid = >journoTown.journoid > LEFT JOIN towns ON journoTown.townid = towns.townid > LEFT JOIN journoRoles ON journoDetails.roleid = >journoRoles.journoRolesid > LEFT JOIN journoNatOpt ON journoDetails.journoid = >journoNatOpt.journoid > LEFT JOIN natOption ON journoNatOpt.natOptid = natOption.natOptid > >WHERE journoDetails.journoid ='#attributes.journoid#' ></cfquery> > > >Thanks > >Dave > >At 15:31 1/23/2003 +0000, you wrote: > > >what's the query? > > > > > >Duncan Cumming > >IT Manager > > > >http://www.alienationdesign.co.uk > >mailto:[EMAIL PROTECTED] > >Tel: 0141 575 9700 > >Fax: 0141 575 9600 > > > >Creative solutions in a technical world > > > >---------------------------------------------------------------------- > >Get your domain names online from: > >http://www.alienationdomains.co.uk > >Reseller options available! > >---------------------------------------------------------------------- > >---------------------------------------------------------------------- > > > > > > > > > > Dave > > Phipps > > > > <dave@phipps- To: ColdFusion User group > > <[EMAIL PROTECTED]> > > cf.co.uk> cc: > > > > Subject: [ cf-dev ] grouping > > > output problem > > 01/23/03 > > > > 03:27 > > PM > > > > Please > > > > respond > > to > > > > dev > > > > > > > > > > > > > > > > > > > > > >Hi, > > > >I am trying to group some output from a query and I can only seem to some > >of it to group properly > > > >I have the following output: > > > ><cfoutput query="journoDetails" group="journoid"> > > <cfif journoDetails.region IS NOT ""> > > <td class="normal" valign="top"><cfoutput > >group="regionid">#journoDetails.region#<br></cfoutput></td> > > </cfif> > > <cfif journoDetails.town IS NOT ""> > > <td class="normal" valign="top"><cfoutput > >group="townid">#journoDetails.town#<br></cfoutput></td> > > </cfif> > > <cfif journoDetails.isNational EQ 1> > > <td class="normal" valign="top"> > > National (Options: <cfif journoDetails.natOpt IS > >NOT "">(<cfoutput group="natOptid"> > >#journoDetails.natOpt#</cfoutput>)<cfelse>None specified.</cfif>) > > </td> > > </cfif> > ></cfoutput> > > > >With my test query I get 4 results for a particular journoid and this >above > > > >code gives something like: > > > >Region: Buckinghamshire<br>Oxfordshire > >Town: Chipping Norton<br>Oxford<br>Chipping Norton<br>Oxford > > > >What I want of course is for only the two regions and two towns to be > >listed. I have tried ordering the query and this causes region to be > >duplicated. > > > >Any help or ideas of a workaround? > > > >Thanks > > > >Dave > > > > > >-- > >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > >To unsubscribe, e-mail: [EMAIL PROTECTED] > >For additional commands, e-mail: [EMAIL PROTECTED] > >For human help, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > >-- > >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > >To unsubscribe, e-mail: [EMAIL PROTECTED] > >For additional commands, e-mail: [EMAIL PROTECTED] > >For human help, e-mail: [EMAIL PROTECTED] > > >============================================ >Phipps CF Development >Oxford, Oxfordshire. >Telephone: +44(0)7718 896696 >http://www.phipps-cf.co.uk >============================================ > > >-- >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >For human help, e-mail: [EMAIL PROTECTED] > > > > > > >-- >** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] >For human help, e-mail: [EMAIL PROTECTED] ============================================ Phipps CF Development Oxford, Oxfordshire. Telephone: +44(0)7718 896696 http://www.phipps-cf.co.uk ============================================ -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
