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