That might work after the cfif conditional statement are included.
However, I chose to try something different after much frustration.
I chose to use a negative "where" condition followed by all "or" clauses.
Let's say the user chose to view only commercial properties.
Then, with the cfif statements included, I'd end up with this SQL:
where 1 = 2
or properties. property_type = 'commercial'
With a condition that is always negative after where,
such as "where 1 = 2", it doesn't trigger any property selections
and the property selections are entirely based on the "or" clauses.
So, this seems to be working correctly.
Let me know if you spot something wrong with my logic!
select properties.property_id, properties.mls_number,
properties.street_number,
properties.street_name,
properties.city, properties.state, properties.remarks,
properties.property_type,
properties.list_price,
properties.listing_office_mls_id, offices.mls, offices.office_name,
offices.display_order,
offices.mls_office_id,
min(property_photos.photo_filename) as prop_photo_filename
from properties
left join property_photos
on substring_index(properties.mls_number, '_', 1) =
property_photos.photo_mls_number
and properties.mls = property_photos.mls
left join offices
on properties.listing_office_mls_id = offices.mls_office_id
and properties.mls = offices.mls
where 1 = 2
<cfif session.lots_land eq 1>
or properties.property_type = 'lots and land'
</cfif>
<cfif session.commercial eq 1>
or properties.property_type = 'commercial'
</cfif>
<cfif session.multifamily eq 1>
or properties.property_type = 'multifamily'
</cfif>
<cfif session.residential eq 1>
or properties.property_type = 'residential'
</cfif>
group by properties.mls_number
order by offices.display_order
> -----Original Message-----
> From: William Seiter [mailto:[EMAIL PROTECTED]
> Sent: Saturday, May 24, 2008 12:23 PM
> To: CF-Talk
> Subject: RE: How can I use conditional statements in this query?
>
> Don't you need a parenthesis in this?
> left join offices
> ::> on properties.listing_office_mls_id =
> ::offices.mls_office_id
> ::> and properties.mls = offices.mls
> ::> where 1 = 1
> ::> and (properties.property_type = 'lots and land'
> ::> or properties.property_type = 'commercial'
> ::> or properties.property_type = 'multifamily'
> ::> or properties.property_type = 'residential')
> ::>
> ::> group by properties.mls_number
> ::> order by offices.display_order
> ----------------------------------
> William Seiter
> ColdFusion Web Developer / Consultant
> http://william.seiter.com
>
>
> Have you ever read a book that changed your life?
> Go to: http://www.winninginthemargins.com
> Use PassKey: GoldenGrove
> You'll be glad you did.
>
>
> ::-----Original Message-----
> ::From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> ::Sent: Friday, May 23, 2008 10:38 PM
> ::To: CF-Talk
> ::Subject: RE: How can I use conditional statements in this query?
> ::
> ::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:
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:306013
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
