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