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:344820 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
