well, actually, the logic wasn't right. the "where" clause needed to be negative, as in "where 1 = 2" followed by "or" conditions for all the "properties.xxxxxxx = 'xxxxxxx'
> -----Original Message----- > From: Rick Faircloth [mailto:[EMAIL PROTECTED] > Sent: Saturday, May 24, 2008 12:41 AM > To: CF-Talk > Subject: RE: How can I use conditional statements in this query? > > Ok... I got it... > > The "where" and "and" clauses have to come after the > last join, as in: > > left join offices > on properties.listing_office_mls_id = > offices.mls_office_id > and properties.mls = offices.mls > where 1 = 1 > > <cfif session.lots_land is '1'> > and properties.property_type = 'lots and land' > </cfif> > > <cfif session.commercial is '1'> > or properties.property_type = 'commercial' > </cfif> > > <cfif session.multifamily is '1'> > or properties.property_type = 'multifamily' > </cfif> > > <cfif session.residential is '1'> > or properties.property_type = 'residential' > </cfif> > > group by properties.mls_number > order by offices.display_order > > Thanks, Will. > > Rick > > > -----Original Message----- > > From: Rick Faircloth [mailto:[EMAIL PROTECTED] > > Sent: Saturday, May 24, 2008 12:31 AM > > To: CF-Talk > > Subject: RE: How can I use conditional statements in this query? > > > > Since the query errors, I can't get the results. However, > > in the CF error, the SQL is shown and I don't see a problem. > > > > However, even if I take out the cfif's and just use > > > > .... > > from properties > > where 1 = 1 > > and 2 = 2 > > left join property_photos > > .... > > > > I still get an error. > > > > It just doesn't like the "where" and "and" clauses. > > > > ??? > > > > > > > > > -----Original Message----- > > > From: Will Tomlinson [mailto:[EMAIL PROTECTED] > > > Sent: Friday, May 23, 2008 11:24 PM > > > To: CF-Talk > > > Subject: Re: How can I use conditional statements in this query? > > > > > > > Hi, all... > > > > > > > > I can't figure out how to use the cfif statements > > > > in the query below. Everywhere I put them I get a syntax > > > > error. DB is MySQL 5. > > > > > > > > Suggestions, anyone? > > > > > > > > > > use your results variable in cfquery to see what SQL is being generated. > > > > > > Will > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:306003 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
