> 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.
yep, that worked ;-) many thanks, Ian W ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 28, 2003 2:20 PM Subject: Re: [ cf-dev ] weird one... > > 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] > -- ** 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]
