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 thisValue>and 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
