you're going to get Town repeated for each Directory. e.g. Directory ID 1, Town = London. Directory ID = 2, Town = London, your query would give you 2 rows, with London repeated twice. You probably want to use <cfoutput group="Town"> on your output of the list of towns.
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! ---------------------------------------------------------------------- ---------------------------------------------------------------------- "Ian Westbrook" To: <[EMAIL PROTECTED]> <[EMAIL PROTECTED] cc: eam.net> Subject: Re: [ cf-dev ] another one... 07/07/2003 15:36 Please respond to dev using 4.5 so don't have CFDUMP... Ian W ----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, July 07, 2003 3:38 PM Subject: RE: [ cf-dev ] another one... > what does a cfdump of the query give you > > -----Original Message----- > From: Ian Westbrook [mailto:[EMAIL PROTECTED] > Sent: 07 July 2003 15:31 > To: cfug dev list > Subject: [ cf-dev ] another one... > > > couldn't get that last one working - worked once then stopped. Oh well. I'll > try again tomorrow. > > on to the next annoyance. trying to output a drop-down list of towns where > there are events hapening in that town in the events Diary. The relevant > tables/fields are: > > table: diary > id > directoryid (= directory.id) > ... > > table: directory > id > town ( = towns.id) > ... > > table: towns > id > town > > this is the query: > > <cfquery datasource="#dsn#" name="gettowns"> > SELECT DISTINCT directory.town AS townid, towns.id, towns.town, > diary.directoryid, directory.id > FROM diary, directory, towns > WHERE diary.directoryid = directory.id > AND directory.town = towns.id > ORDER BY towns.town > </cfquery> > > and this is the output: > > <select name="town"> > <option value="all">all towns/cities...</option> > <cfoutput query="gettowns"> > <cfif NOT ISDefined("url.townid")> > <option value="#townid#">#town#</option> > <cfelse> > <option value="#townid#"<cfif gettowns.townid IS url.townid> > selected</cfif>>#town#</option> > </cfif> > </cfoutput> > </select> > > it's all working fine, except that I'm getting a town's name output twice on > the drop-down, and I only want it the once (which is where the DISTINCT is > meant to come in) > > see it here: > http://test.artsoutheast.co.uk/diarysearch.cfm?CFID=1290809&CFTOKEN=50249308 > &sortby=quick&townid=21 > > I've tried making other items DISTINCT, but I can't seem to get it to work > anywhichway... > > can anyone put me out of my misery? If I had any hair left I'd be pulling it > out... > > Ian W > > > Ian Westbrook, > FutureDream Media Limited, > (W): www.futuredream.net > (E): [EMAIL PROTECTED] > (T): +44 (0) 1303 258 985 > (M): 07939 510 812 > > This message may contain information which is legally privileged and/or > confidential. If you are not the intended recipient, you are hereby notified > that any unauthorised disclosure, copying, distribution or use of this > information is strictly prohibited. Such notification notwithstanding any > comments, opinions, information or conclusions expressed in this message are > those of the originator, not of FutureDream Media Ltd, unless otherwise > explicitly and independently indicated by an authorised representative of > FutureDream Media Ltd. > > -- > ** 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] -- ** 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]
