Break it into 2 queries. <CFQUERY Name="GetWaivers1" Datasource="#DSN#"> Select RW.ClientID from RentalWaivers RW where Month(RW.RentalWaiverDate) = #Form.Month# and Year(RW.RentalWaiverDate) = #Form.Year#) </CFQUERY>
<cfset filter = valuelist(getWaivers1,clientID)> <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 (#filter#) </CFQUERY> > -----Original Message----- > From: Rick Faircloth [mailto:[EMAIL PROTECTED]] > Sent: Thursday, December 19, 2002 1:50 AM > To: CF-Talk > Subject: How to do this without the subselect? > > > 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!) > > Thanks, > > Rick > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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