RE: Conditional SQL
I don't think that is a very good solution. Not only can this not use any indexes, it will also not match Friends Season 7 - Complete Series because the order of the words doesn't match. I was originally breaking the string and doing several 'and's. I do need to look into Full Text Indexing but I haven't used it before, any good reference sites as a starting point? ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281116 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Conditional SQL
James Smith wrote: I don't think that is a very good solution. Not only can this not use any indexes, it will also not match Friends Season 7 - Complete Series because the order of the words doesn't match. I was originally breaking the string and doing several 'and's. I do need to look into Full Text Indexing but I haven't used it before, any good reference sites as a starting point? Full text searching is so implementation specific that you might as well start with the manual. Jochem ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281140 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Conditional SQL
AND ProductGroupID = CASE WHEN @productgroupid 0 THEN @productgroupid ELSE ProductGroupID END This is the simplest to read but has a drawback, it the stored ProductGroupID is null then the statement becomes AND ProductGroupID = ProductGroupID and for some reason NULL does not equal NULL so the row isn't returned, is there a workaround for this? -- Jay ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280967 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Conditional SQL
On 6/13/07, James Smith [EMAIL PROTECTED] wrote: AND ProductGroupID = CASE WHEN @productgroupid 0 THEN @productgroupid ELSE ProductGroupID END This is the simplest to read but has a drawback, it the stored ProductGroupID is null then the statement becomes AND ProductGroupID = ProductGroupID and for some reason NULL does not equal NULL so the row isn't returned, is there a workaround for this? AND COALESCE(ProductGroupID,0) = CASE WHEN @productgroupid 0 THEN @productgroupid ELSE COALESCE(ProductGroupID,0) END ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280968 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Conditional SQL
This seems like a really complicated way of saying AND ((ProductGroupID = @productgroupid) OR (@productgroupid = 0)). Am I missing something? -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 13, 2007 7:31 AM To: CF-Talk Subject: Re: Conditional SQL On 6/13/07, James Smith [EMAIL PROTECTED] wrote: AND ProductGroupID = CASE WHEN @productgroupid 0 THEN @productgroupid ELSE ProductGroupID END This is the simplest to read but has a drawback, it the stored ProductGroupID is null then the statement becomes AND ProductGroupID = ProductGroupID and for some reason NULL does not equal NULL so the row isn't returned, is there a workaround for this? AND COALESCE(ProductGroupID,0) = CASE WHEN @productgroupid 0 THEN @productgroupid ELSE COALESCE(ProductGroupID,0) END ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280976 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Conditional SQL
On 6/13/07, Gaulin, Mark [EMAIL PROTECTED] wrote: This seems like a really complicated way of saying AND ((ProductGroupID = @productgroupid) OR (@productgroupid = 0)). Am I missing something? I think really complicated is a bit harsh...perhaps more complicated...and the previous version doesn't care what the value of @productgroupid is, unless it is greater than 0...it could be 0 or -1 or NULL and return all records...in your example it must be 0 to return all recordswhich may be fine depending on what you are expecting as inputs. The following should work the same as my previous statement AND ((@productgroupid = 0) OR (@productgroupid IS NULL) OR (ProductGroupID = @productgroupid)) And all of these examples, while fun exercises, ignore the fact that the conditional processing still may not be faster in SQL, as Jochem pointed out. ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280991 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Conditional SQL
This seems like a really complicated way of saying AND ((ProductGroupID = @productgroupid) OR (@productgroupid = 0)). Am I missing something? Works perfectly... Simplest solutions are always the best, cheers. -- Jay ~| CF 8 â Scorpio beta now available, easily build great internet experiences â Try it now on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280994 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Conditional SQL
As a last resort this procedure does the following... SET @searchTerm = '%' + REPLACE(@searchTerm,' ','%') + '%'; .. .. .. AND (Title LIKE @searchTerm OR ArtistName LIKE @searchTerm) So that is someone searches for 'friends series 7' it is first turned into '%friends%series%7%' which will match the title 'friends complete series - 7' (for example). This is however, very slow. Is there a way to speed up this sort of LIKE matching? -- Jay ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281031 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Conditional SQL
I don't know how to make LIKE faster. I've heard that MySQL has full text indexing and searching built in and with SQL Server you can use Full Text Indexing, but it is a bit more complicated to set up, maintain, and query than I would like, but it does work. You have to use a full text searching query (CONTAINS) and not LIKE. Mark -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 13, 2007 11:56 AM To: CF-Talk Subject: RE: Conditional SQL As a last resort this procedure does the following... SET @searchTerm = '%' + REPLACE(@searchTerm,' ','%') + '%'; .. ... ... AND (Title LIKE @searchTerm OR ArtistName LIKE @searchTerm) So that is someone searches for 'friends series 7' it is first turned into '%friends%series%7%' which will match the title 'friends complete series - 7' (for example). This is however, very slow. Is there a way to speed up this sort of LIKE matching? -- Jay ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281038 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Conditional SQL
On 6/13/07, James Smith [EMAIL PROTECTED] wrote: As a last resort this procedure does the following... SET @searchTerm = '%' + REPLACE(@searchTerm,' ','%') + '%'; .. .. .. AND (Title LIKE @searchTerm OR ArtistName LIKE @searchTerm) So that is someone searches for 'friends series 7' it is first turned into '%friends%series%7%' which will match the title 'friends complete series - 7' (for example). This is however, very slow. Is there a way to speed up this sort of LIKE matching? -- Jay Jay, If you are trying to do a keyword search, it would be better to use SQL Server's Full Text Search capability instead of using LIKE. Dean -- __ Dean Lawrence, CIO/Partner Internet Data Technology 888.GET.IDT1 ext. 701 * fax: 888.438.4381 http://www.idatatech.com/ Corporate Internet Development and Marketing Specialists ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281042 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Conditional SQL
Or a search engine. This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Dean Lawrence To: CF-Talk Sent: Wed Jun 13 17:32:04 2007 Subject: Re: Conditional SQL On 6/13/07, James Smith [EMAIL PROTECTED] wrote: As a last resort this procedure does the following... SET @searchTerm = '%' + REPLACE(@searchTerm,' ','%') + '%'; .. .. .. AND (Title LIKE @searchTerm OR ArtistName LIKE @searchTerm) So that is someone searches for 'friends series 7' it is first turned into '%friends%series%7%' which will match the title 'friends complete series - 7' (for example). This is however, very slow. Is there a way to speed up this sort of LIKE matching? -- Jay Jay, If you are trying to do a keyword search, it would be better to use SQL Server's Full Text Search capability instead of using LIKE. Dean -- __ Dean Lawrence, CIO/Partner Internet Data Technology 888.GET.IDT1 ext. 701 * fax: 888.438.4381 http://www.idatatech.com/ Corporate Internet Development and Marketing Specialists ~| ColdFusion 8 beta â Build next generation applications today. Free beta download on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281045 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Conditional SQL
James Smith wrote: As a last resort this procedure does the following... SET @searchTerm = '%' + REPLACE(@searchTerm,' ','%') + '%'; .. .. .. AND (Title LIKE @searchTerm OR ArtistName LIKE @searchTerm) So that is someone searches for 'friends series 7' it is first turned into '%friends%series%7%' which will match the title 'friends complete series - 7' (for example). I don't think that is a very good solution. Not only can this not use any indexes, it will also not match Friends Season 7 - Complete Series because the order of the words doesn't match. Jochem ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:281060 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Conditional SQL
I currently have a (very complex) query in the format... SELECT Title, ProductID FROM aTable WHERE Stock 0 cfif len(trim(queryParams.productgroupid)) GT 0 AND ProductGroupID = #val(productgroupid)# /cfif I wish to move this query into MSSQL server for performance reasons, how do I go about running the conditional code? I have tried... DECLARE @productgroupid bigint; SET @productgroupid = 5; SELECT Title, ProductID FROM aTable WHERE Stock 0 IF @productgroupid 0 AND m.ProductGroupID = @productgroupid But just get syntax errors, is there a way to get the IF...ELSE into the sql or do I have to reformat it into... IF @productgroupid 0 SELECT fields FROM tables WHERE someCondition AND anotherCondition ELSE SELECT fields FROM tables WHERE someCondition Because that is going to make for some really unreadable and LONG code once all my conditions are coded for! -- Jay ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280794 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Conditional SQL
Try this: DECLARE @productgroupid bigint; SET @productgroupid = 5; SELECT Title, ProductID FROM aTable WHERE Stock 0 AND (m.ProductGroupID = @productgroupid AND @productgroupid 0) Koen ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280795 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Conditional SQL
You can do the following cfparam name=queryParams.productgroupid default=-10001 and then run the query as cfquery DECLARE @productgroupid int; SET @productgroupid = #queryParams.productgroupid#; SELECT Title, ProductID FROM aTable WHERE Stock 0 AND m.ProductGroupID = coalesce(nullIf(@productgroupid,-10001),m.ProductGroupID) /cfquery What that will do is the following: if @productgroupid is -10001 it will set it to null, and then the coalesce will set the right side of the where clause to m.ProductGroupID which will mean the where clause says AND m.ProductGroupID = m.ProductGroupID which is always true and therefore not limiting. If however its not -10001, then it will run the actual query as you want it to. I currently have a (very complex) query in the format... SELECT Title, ProductID FROM aTable WHERE Stock 0 cfif len(trim(queryParams.productgroupid)) GT 0 AND ProductGroupID = #val(productgroupid)# /cfif I wish to move this query into MSSQL server for performance reasons, how do I go about running the conditional code? I have tried... DECLARE @productgroupid bigint; SET @productgroupid = 5; SELECT Title, ProductID FROM aTable WHERE Stock 0 IF @productgroupid 0 AND m.ProductGroupID = @productgroupid But just get syntax errors, is there a way to get the IF...ELSE into the sql or do I have to reformat it into... IF @productgroupid 0 SELECT fields FROM tables WHERE someCondition AND anotherCondition ELSE SELECT fields FROM tables WHERE someCondition Because that is going to make for some really unreadable and LONG code once all my conditions are coded for! -- Jay ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280797 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Conditional SQL
You could use the CASE statement instead. Or you could write a stored procedure which does exactly the thing you want. DECLARE @productgroupid bigint; SET @productgroupid = 5; IF @productgroupid 0 BEGIN SELECT Title, ProductID FROM aTable WHERE Stock 0 AND m.ProductGroupID = @productgroupid END ELSE BEGIN SELECT Title, ProductID FROM aTable WHERE Stock 0 AND SOMTETHINGELSE END Greetings / Grüsse Gert Franz Customer Care Railo Technologies GmbH [EMAIL PROTECTED] www.railo.ch Join our Mailing List / Treten Sie unserer Mailingliste bei: deutsch: http://de.groups.yahoo.com/group/railo/ english: http://groups.yahoo.com/group/railo_talk/ James Smith schrieb: I currently have a (very complex) query in the format... SELECT Title, ProductID FROM aTable WHERE Stock 0 cfif len(trim(queryParams.productgroupid)) GT 0 AND ProductGroupID = #val(productgroupid)# /cfif I wish to move this query into MSSQL server for performance reasons, how do I go about running the conditional code? I have tried... DECLARE @productgroupid bigint; SET @productgroupid = 5; SELECT Title, ProductID FROM aTable WHERE Stock 0 IF @productgroupid 0 AND m.ProductGroupID = @productgroupid But just get syntax errors, is there a way to get the IF...ELSE into the sql or do I have to reformat it into... IF @productgroupid 0 SELECT fields FROM tables WHERE someCondition AND anotherCondition ELSE SELECT fields FROM tables WHERE someCondition Because that is going to make for some really unreadable and LONG code once all my conditions are coded for! -- Jay ~| ColdFusion 8 beta â Build next generation applications today. Free beta download on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280800 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Conditional SQL
I wish to move this query into MSSQL server for performance reasons, how do I go about running the conditional code? I have tried... I'm not sure you are going to get the performance benefit you are looking for. Doing the conditional processing in SQL isn't necessarily going to be faster...but you could do... DECLARE @productgroupid bigint; SET @productgroupid = 5; SELECT Title, ProductID FROM aTable WHERE Stock 0 AND ProductGroupID = CASE WHEN @productgroupid 0 THEN @productgroupid ELSE ProductGroupID END ~| ColdFusion MX7 and Flex 2 Build sales marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280801 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Conditional SQL
James Smith wrote: SELECT Title, ProductID FROM aTable WHERE Stock 0 cfif len(trim(queryParams.productgroupid)) GT 0 AND ProductGroupID = #val(productgroupid)# /cfif I wish to move this query into MSSQL server for performance reasons Which performance reasons? Why do you expect an improvement of the performance from moving the condition to the DB? Jochem ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280806 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Conditional SQL
the query is actually very complex involving several loops and a few conditional clauses and I am hoping that by sticking it all into a stored procedure it will be precompiled by MSSQL and therefore more efficient. On 12/06/07, Jochem van Dieten [EMAIL PROTECTED] wrote: James Smith wrote: SELECT Title, ProductID FROM aTable WHERE Stock 0 cfif len(trim(queryParams.productgroupid)) GT 0 AND ProductGroupID = #val(productgroupid)# /cfif I wish to move this query into MSSQL server for performance reasons Which performance reasons? Why do you expect an improvement of the performance from moving the condition to the DB? Jochem ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280812 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Conditional SQL
James Smith wrote: the query is actually very complex involving several loops and a few conditional clauses Your query has several loops? You mean you actually use the new hierargical query features of MS SQL 2005? Or does the CF code that generates the query involve several loops and conditions? and I am hoping that by sticking it all into a stored procedure it will be precompiled by MSSQL and therefore more efficient. So what part of the current execution time is spent in the compilation step? Because that is the maximum you will gain from it being precompiled. Jochem ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280833 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Conditional SQL Query Not Working
I have a form, that asks for a number of 'conditions' in order to find an
attendee for a course. The problem is, I've put together a query that
returns too many results / not the correct results. Every row returned is
returned multiple times. I'm wondering if I'm needing a join somewhere...
or if there is a more efficient way to make this work. Thank you in advance
for your help!
cfquery name=criteria datasource=#DSN#
SELECT *
FROMattendee,
users,
courses,
organizations
WHERE attendee.users_id = users.id
AND attendee.assigneddate = courses.id
!--- Limit query results by profession ---
cfif form.profession NEQ 'all'
AND users.profession = '#form.profession#'
/cfif
!--- Limit query results by progress in program ---
cfif form.progress EQ 'certified'
AND attendee.gradnum 'Pending'
cfelseif form.progress EQ 'failed'
AND attendee.postassign = 'Failed'
cfelseif form.progress EQ 'incomplete'
AND attendee.gradnum = 'Pending'
cfelseif form.progress EQ 'withdrawn'
AND attendee.withdrawn = '1'
/cfif
!--- Limit query results by organization training was
received from ---
cfif form.org_id NEQ 'all'
AND courses.org = #form.org_id#
/cfif
!--- Limit query results by date ---
cfif (len(form.start_date) GT 1) AND (len(form.end_date) GT
1)
AND courses.s1_startdate #form.start_date#
AND courses.s1_enddate #form.end_date#
/cfif
ORDER BY
users.lname ASC
/cfquery
*** *** ***
The query:
*** *** ***
The form:
form name=find_attendee action=report_attendee.cfm method=post
table
tr
td width=125Profession:/td
td colspan=3
select name=profession
option value=allAll/option
cfoutput query=profession
option
value=#profession.profession##profession.profession#/option
/cfoutput
/select
/td
/tr
tr
td width=125Start Date:/td
td width=175input type=text name=start_datenbsp;a
href=javascript:ShowCalendar('find_attendee', 'start_date')img
src=images/cal.gif width=16 height=16 border=0 alt=Click Here to
Pick up the date/a/td
td width=125End Date:/td
td width=175input type=text name=end_datenbsp;a
href=javascript:ShowCalendar('find_attendee', 'end_date')img
src=images/cal.gif width=16 height=16 border=0 alt=Click Here to
Pick up the date/a/td
/tr
tr
td width=125Took Training From:/td
td colspan=3
select name=org_id
option value=allAll/option
cfoutput query=org_admin
option value=#org_admin.id##org_admin.org_abbr#/option
/cfoutput
/select
/td
/tr
tr
td width=125Progress:/td
td colspan=3
select name=progress
option value=allAll/option
option value=certifiedCertified/option
option value=failedFailed/option
option value=incompleteIncomplete/option
option value=withdrawnWithdrawn/option
/select
/td
/tr
tr
td width=125nbsp/td
td colspan=3 align=leftinput type=submit name=submit
value=Search!/td
/tr
/form
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Conditional SQL Query Not Working
Jillian Carroll wrote: I have a form, that asks for a number of 'conditions' in order to find an attendee for a course. The problem is, I've put together a query that returns too many results / not the correct results. Every row returned is returned multiple times. I'm wondering if I'm needing a join somewhere... Yes. You need to join organizations, probably to courses. Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Conditional SQL Query Not Working
Wow... join conditions in the where clause... Haven't seen those for years
:)
Looks like you have a cartesian join between organizations and the rest of
your dataset... Need to join organizations to something.
-Original Message-
From: Jillian Carroll [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 03, 2003 6:17 AM
To: CF-Talk
Subject: Conditional SQL Query Not Working
I have a form, that asks for a number of 'conditions' in order to find an
attendee for a course. The problem is, I've put together a query that
returns too many results / not the correct results. Every row returned is
returned multiple times. I'm wondering if I'm needing a join somewhere...
or if there is a more efficient way to make this work. Thank you in advance
for your help!
cfquery name=criteria datasource=#DSN#
SELECT *
FROMattendee,
users,
courses,
organizations
WHERE attendee.users_id = users.id
AND attendee.assigneddate = courses.id
!--- Limit query results by profession ---
cfif form.profession NEQ 'all'
AND users.profession = '#form.profession#'
/cfif
!--- Limit query results by progress in program ---
cfif form.progress EQ 'certified'
AND attendee.gradnum 'Pending'
cfelseif form.progress EQ 'failed'
AND attendee.postassign = 'Failed'
cfelseif form.progress EQ 'incomplete'
AND attendee.gradnum = 'Pending'
cfelseif form.progress EQ 'withdrawn'
AND attendee.withdrawn = '1'
/cfif
!--- Limit query results by organization training was
received from ---
cfif form.org_id NEQ 'all'
AND courses.org = #form.org_id#
/cfif
!--- Limit query results by date ---
cfif (len(form.start_date) GT 1) AND (len(form.end_date) GT
1)
AND courses.s1_startdate #form.start_date#
AND courses.s1_enddate #form.end_date#
/cfif
ORDER BY
users.lname ASC
/cfquery
*** *** ***
The query:
*** *** ***
The form:
form name=find_attendee action=report_attendee.cfm method=post
table tr
td width=125Profession:/td
td colspan=3
select name=profession
option value=allAll/option
cfoutput query=profession
option
value=#profession.profession##profession.profession#/option
/cfoutput
/select
/td
/tr
tr
td width=125Start Date:/td
td width=175input type=text name=start_datenbsp;a
href=javascript:ShowCalendar('find_attendee', 'start_date')img
src=images/cal.gif width=16 height=16 border=0 alt=Click Here to
Pick up the date/a/td
td width=125End Date:/td
td width=175input type=text name=end_datenbsp;a
href=javascript:ShowCalendar('find_attendee', 'end_date')img
src=images/cal.gif width=16 height=16 border=0 alt=Click Here to
Pick up the date/a/td /tr tr
td width=125Took Training From:/td
td colspan=3
select name=org_id
option value=allAll/option
cfoutput query=org_admin
option value=#org_admin.id##org_admin.org_abbr#/option
/cfoutput
/select
/td
/tr
tr
td width=125Progress:/td
td colspan=3
select name=progress
option value=allAll/option
option value=certifiedCertified/option
option value=failedFailed/option
option value=incompleteIncomplete/option
option value=withdrawnWithdrawn/option
/select
/td
/tr
tr
td width=125nbsp/td
td colspan=3 align=leftinput type=submit name=submit
value=Search!/td /tr /form
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
