Rick Faircloth wrote: > Hi, all. > > How can I get the same results as this query with using the subselect? > > <CFQUERY Name="GetWaivers" Datasource="#DSN#"> > > Select UW.UtilityWaiverID > from utilitywaivers UW > where Month(UW.IssueDate) = #Form.Month# > and Year(UW.IssueDate) = #Form.Year# > and UW.ClientID not in > (Select RW.ClientID from RentalWaivers RW > where Month(RW.RentalWaiverDate) = #Form.Month# > and Year(RW.RentalWaiverDate) = #Form.Year#) > > </CFQUERY> > > (I'll be soooo glad when they get subselects in mySQL 4.2!)
do a left outer join on RentalWaivers in the query and then do a check that where UW.ClientID is null (if, this is standard perfomance improvement anyway as it uses a index...) "not in" is actually a real bad performance killer... this technique is found in most good oracle books... should work for mysql but i don't know z ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
