could i suggest removing the column diary.reviewid from the query. it's not needed as you've already got reviews.reviewid (incidentally the use of the alias on this column is also unnecessary).
what you could do is add reviewid to your ORDER BY clause. then just group on the reviewid in your output. that way you eliminate the need for the distinct, and you don't get repeating rows. alternatively, just select the reviewid in this first query, (with distinct), then when you output the results, do a new query each iteration to get the details of the review. 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 ] weird one... 28/08/2003 14:07 Please respond to dev the query is now: <cfquery datasource="#dsn#" name="getreviews"> SELECT DISTINCT reviews.reviewid AS reviewid, reviews.userid, reviews.id, reviews.companyid AS companyid, reviews.review, reviews.publish, reviews.dateadded, diary.reviewid, diary.title, directory.id, directory.fullname FROM reviews, diary, directory <cfif IsDefined("url.reviewid")> WHERE reviews.reviewid = #url.reviewid# <cfelse> WHERE reviews.reviewid = #form.reviewid# </cfif> AND reviews.publish = 'yes' AND reviews.companyid = directory.id AND reviews.reviewid = diary.reviewid ORDER BY reviews.dateadded </cfquery> reviews.review is a memo field, but reviews.reviewid is a number... and I've never seen truncation like that before, either. as I say, if I take the DISTINCT away I get the full review, but three times. very odd... Ian W ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 28, 2003 1:41 PM Subject: Re: [ cf-dev ] weird one... > > Correct me if I'm wrong, but I thought you couldn't do a SELECT DISTINCT > with memo fields? I'm assuming review is a memo field, not a text field. > Not sure though why that would just truncate it rather than throw an error. > > > 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 ] weird one... > > 28/08/2003 > 13:30 > Please > respond to > dev > > > > > > > what's your database? > > Access db, CF4.5 > > Ian > > ----- Original Message ----- > From: <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, August 28, 2003 12:40 PM > Subject: Re: [ cf-dev ] weird one... > > > > > > what's your database? > > > > > > 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: "cfug dev list" > <[EMAIL PROTECTED]> > > <[EMAIL PROTECTED] cc: > > eam.net> Subject: [ cf-dev ] weird > one... > > > > 28/08/2003 > > 12:28 > > Please > > respond to > > dev > > > > > > > > > > > > got a weird one here. > > > > this is the query: > > > > <cfquery datasource="#dsn#" name="getreviews"> > > SELECT DISTINCT reviews.reviewid AS reviewid, diary.reviewid, > > reviews.companyid AS companyid, reviews.*, diary.title, directory.id, > > directory.fullname > > FROM reviews, diary, directory > > <cfif IsDefined("url.reviewid")> > > WHERE reviews.reviewid = #url.reviewid# > > <cfelse> > > WHERE reviews.reviewid = #form.reviewid# > > </cfif> > > AND reviews.publish = 'yes' > > AND reviews.companyid = directory.id > > AND reviews.reviewid = diary.reviewid > > ORDER BY reviews.dateadded > > </cfquery> > > > > > > but the review that this outputs is truncated and gets cut off. > > > > If I remove the DISTINCT I get the full review as I should, but I get it > > three times. > > > > there's nothing weird about the output, just a standard: > > > > <tr bgcolor="#bg#"> > > <td align="right" valign="top"><b>Review:</b></td> > > <td>#Replace(review, Chr(13), "<br>", "ALL")#<br><br></td> > > </tr> > > > > > > anyone any ideas? > > > > TIA > > > > Ian W > > > > > > > > > > > > -- > > ** 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] > -- ** 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]
