RE: SQL Quandary
That's perfect, thank you James! -Original Message- From: James Holmes [mailto:james.hol...@gmail.com] Sent: 23 May 2011 03:12 To: cf-talk Subject: Re: SQL Quandary It can be. Taking your last example: IF (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 select projected from tbl_stockItems where projected 10 else select projected from tbl_stockItems where projected 10 This can be written as: select projected from tbl_stockItems where ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 AND projected 10 ) OR ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) 1 AND projected 10 ) Since the subquery clause can only be true for one of the AND clauses, only one set of results will be returned. No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3654 - Release Date: 05/22/11 19:33:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344835 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
Simple and elegant. On Sun, May 22, 2011 at 10:11 PM, James Holmes james.hol...@gmail.comwrote: It can be. Taking your last example: IF (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 select projected from tbl_stockItems where projected 10 else select projected from tbl_stockItems where projected 10 This can be written as: select projected from tbl_stockItems where ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 AND projected 10 ) OR ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) 1 AND projected 10 ) Since the subquery clause can only be true for one of the AND clauses, only one set of results will be returned. -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 23 May 2011 09:29, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: because the intention is not a simple WHERE search expression. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344845 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
SQL Quandary
Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344815 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
It would be easier to refer to this 3rd table if you supply the table.columnname so we know what were talking about. what is the relationship between this table and the other tables in the query, and from where does the value come that you want to compare it with. On Sun, May 22, 2011 at 4:46 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344816 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
What Russ wrote regarding your parameters table. I've not got an SQL server box booted up to check, but the equivalent of the following sort of thing works fine in MySQL: SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID INNER JOIN dbo.tbl_parameters ON dbo.tbl_parameters.someKey = 'some value' where stockID 0 AND (dbo.tbl_parameters.someField != '#thisValue#' OR stockitems.Projected 0) As long as your parameters table join condition only ever matches one record, you should be fine. -- Pete Jordan Horus Web Engineering Ltd 90 Belvoir Street Hull HU5 3LR p: 01482 446471 m: 07973 725120 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344817 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
Hi Pete, Thanks for the reply ... If only it were that straightforward :) What I need to do is change the parameters of the search on the first tables depending on various conditions of a column in a table not otherwise included in the query. I have a feeling CASE will do it somehow, but I can't get the syntax. Jenny -Original Message- From: Pete Jordan [mailto:houseoffus...@skydancer.org.uk] Sent: 22 May 2011 19:00 To: cf-talk Subject: Re: SQL Quandary What Russ wrote regarding your parameters table. I've not got an SQL server box booted up to check, but the equivalent of the following sort of thing works fine in MySQL: SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID INNER JOIN dbo.tbl_parameters ON dbo.tbl_parameters.someKey = 'some value' where stockID 0 AND (dbo.tbl_parameters.someField != '#thisValue#' OR stockitems.Projected 0) As long as your parameters table join condition only ever matches one record, you should be fine. -- Pete Jordan Horus Web Engineering Ltd 90 Belvoir Street Hull HU5 3LR p: 01482 446471 m: 07973 725120 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344819 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
Hi Russ, Thanks for the reply ... 3 tables, for example. Table A, productsm Table B product options, linked on the stockID. Table C, some site parameters. So it's: Select stuff from Table and Table B, but if a value changes in a column in table C, run a different selection. IF table_C.column = this value, add something to the WHERE, but IF table_C.column = some other value, add something else to the WHERE. Like I say, doing it by returning all the results to CF and then using CFIF/where clauses works fine, I just want it all to happen in SQL, if possible. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 22 May 2011 18:20 To: cf-talk Subject: Re: SQL Quandary It would be easier to refer to this 3rd table if you supply the table.columnname so we know what were talking about. what is the relationship between this table and the other tables in the query, and from where does the value come that you want to compare it with. On Sun, May 22, 2011 at 4:46 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344820 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
if there is no relationship between tableC and the other tables, which seems to be the case, then there is no way to JOIN then thus I cannot see how you can directly influence the result set. If you are simply needing to change the query based on a single value, then you could do it as a stored procedure and then pass in the value from table C as a parameter to dynamically build your where clause using CASE statements this way. On Sun, May 22, 2011 at 8:37 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Hi Russ, Thanks for the reply ... 3 tables, for example. Table A, productsm Table B product options, linked on the stockID. Table C, some site parameters. So it's: Select stuff from Table and Table B, but if a value changes in a column in table C, run a different selection. IF table_C.column = this value, add something to the WHERE, but IF table_C.column = some other value, add something else to the WHERE. Like I say, doing it by returning all the results to CF and then using CFIF/where clauses works fine, I just want it all to happen in SQL, if possible. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 22 May 2011 18:20 To: cf-talk Subject: Re: SQL Quandary It would be easier to refer to this 3rd table if you supply the table.columnname so we know what were talking about. what is the relationship between this table and the other tables in the query, and from where does the value come that you want to compare it with. On Sun, May 22, 2011 at 4:46 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344824 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
Thanks Russ, I agree, I can't see a way around it using CASE or JOINS. The closest I can get to what I want is this: IF (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 select projected from tbl_stockItems where projected 10 else select projected from tbl_stockItems where projected 10 That SQL isn't exactly it, of course, but it demonstrates the aim. I have this nagging feeling it could be done in a better way, but at least this solution means the work is done by SQL and not after it gets to CF. I'm going to load up some test data and see if it actually runs faster then using CFIF's in CF after the initial query is run. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 23 May 2011 00:14 To: cf-talk Subject: Re: SQL Quandary if there is no relationship between tableC and the other tables, which seems to be the case, then there is no way to JOIN then thus I cannot see how you can directly influence the result set. If you are simply needing to change the query based on a single value, then you could do it as a stored procedure and then pass in the value from table C as a parameter to dynamically build your where clause using CASE statements this way. On Sun, May 22, 2011 at 8:37 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Hi Russ, Thanks for the reply ... 3 tables, for example. Table A, productsm Table B product options, linked on the stockID. Table C, some site parameters. So it's: Select stuff from Table and Table B, but if a value changes in a column in table C, run a different selection. IF table_C.column = this value, add something to the WHERE, but IF table_C.column = some other value, add something else to the WHERE. Like I say, doing it by returning all the results to CF and then using CFIF/where clauses works fine, I just want it all to happen in SQL, if possible. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 22 May 2011 18:20 To: cf-talk Subject: Re: SQL Quandary It would be easier to refer to this 3rd table if you supply the table.columnname so we know what were talking about. what is the relationship between this table and the other tables in the query, and from where does the value come that you want to compare it with. On Sun, May 22, 2011 at 4:46 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344826 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
well don't forget you have query of queries, this works really well if you can cache the original query, then it is really fast. On Mon, May 23, 2011 at 12:54 AM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Thanks Russ, I agree, I can't see a way around it using CASE or JOINS. The closest I can get to what I want is this: IF (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 select projected from tbl_stockItems where projected 10 else select projected from tbl_stockItems where projected 10 That SQL isn't exactly it, of course, but it demonstrates the aim. I have this nagging feeling it could be done in a better way, but at least this solution means the work is done by SQL and not after it gets to CF. I'm going to load up some test data and see if it actually runs faster then using CFIF's in CF after the initial query is run. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 23 May 2011 00:14 To: cf-talk Subject: Re: SQL Quandary if there is no relationship between tableC and the other tables, which seems to be the case, then there is no way to JOIN then thus I cannot see how you can directly influence the result set. If you are simply needing to change the query based on a single value, then you could do it as a stored procedure and then pass in the value from table C as a parameter to dynamically build your where clause using CASE statements this way. On Sun, May 22, 2011 at 8:37 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Hi Russ, Thanks for the reply ... 3 tables, for example. Table A, productsm Table B product options, linked on the stockID. Table C, some site parameters. So it's: Select stuff from Table and Table B, but if a value changes in a column in table C, run a different selection. IF table_C.column = this value, add something to the WHERE, but IF table_C.column = some other value, add something else to the WHERE. Like I say, doing it by returning all the results to CF and then using CFIF/where clauses works fine, I just want it all to happen in SQL, if possible. Jenny -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 22 May 2011 18:20 To: cf-talk Subject: Re: SQL Quandary It would be easier to refer to this 3rd table if you supply the table.columnname so we know what were talking about. what is the relationship between this table and the other tables in the query, and from where does the value come that you want to compare it with. On Sun, May 22, 2011 at 4:46 PM, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Scenario. I have a key query taking data from about 4 tables to produce a record set. I would like to be able to add a where clause to a column in a table not included in the query. Something like this much simplified breakdown: Three tables involved, two in the initial query, and a third table not in the query, but which has a parameter I need to use. Doing this by running the query to CF and then using CFIF's would be easy, but I'd to run all of the query in pure SQL. If it was done using CF it would like like this:- SELECT dbo.tbl_stock.stockID, dbo.tbl_stockItems.stockItemID, dbo.tbl_stockItems.projected FROM dbo.tbl_stock INNER JOIN dbo.tbl_stockItems ON dbo.tbl_stock.stockID = dbo.tbl_stockItems.stockID where stockID 0 Cfif params.someField is thisValueand stockitems.Projected 0/cfif Hope I've explained myself clearly. I've tried using CASE, for example, but as soon as I add the params table it creates a cross join. The reason behind wanting to do it this way is for performance gain and to simplify use of the query when it gets to CF. Any ideas, please? Thanks in advance, Jenny Jenny Gavin-Wear Fast Track Online Tel: 01262 602013 http://www.fasttrackonline.co.uk/ No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344828 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
Why not just add the subquery in as part of the where clause for each type of record you want? On Monday, 23 May 2011, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Thanks Russ, I agree, I can't see a way around it using CASE or JOINS. -- -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344829 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
Sure, I use QofQ a lot. What I wanted from this solution was to reduce the number of records being returned by the query before it even got to CF. -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: 23 May 2011 01:22 To: cf-talk Subject: Re: SQL Quandary well don't forget you have query of queries, this works really well if you can cache the original query, then it is really fast. No virus found in this outgoing message. Checked by AVG - www.avg.com Version: 9.0.901 / Virus Database: 271.1.1/3652 - Release Date: 05/21/11 19:34:00 ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344830 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: SQL Quandary
because the intention is not a simple WHERE search expression. The idea is to change the search expression completely depending on a value in a table not in the actual query. -Original Message- From: James Holmes [mailto:james.hol...@gmail.com] Sent: 23 May 2011 01:47 To: cf-talk Subject: Re: SQL Quandary Why not just add the subquery in as part of the where clause for each type of record you want? On Monday, 23 May 2011, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: Thanks Russ, I agree, I can't see a way around it using CASE or JOINS. -- -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344831 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Quandary
It can be. Taking your last example: IF (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 select projected from tbl_stockItems where projected 10 else select projected from tbl_stockItems where projected 10 This can be written as: select projected from tbl_stockItems where ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) = 1 AND projected 10 ) OR ( (SELECT checkProjected FROM tbl_Params WHERE siteID = 1) 1 AND projected 10 ) Since the subquery clause can only be true for one of the AND clauses, only one set of results will be returned. -- WSS4CF - WS-Security framework for CF http://wss4cf.riaforge.org/ On 23 May 2011 09:29, Jenny Gavin-Wear jenn...@fasttrackonline.co.uk wrote: because the intention is not a simple WHERE search expression. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344832 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
