>ok i am trying to get this down & am running into problems >Any Help would be greatly appreciated. > >First in rsStudioIN.StudioID i am trying to generate a list of id's that >where created this month >it seems to being working, but when i call rsStudioIN.StudioID in my next >Query, it only takes the first ID, Not A list... > >but if i ><cfloop query="rsStudioIN"> ><cfoutput>#StudioID#</cfoutput> ></cfloop> >then it shows all the correct ids..? > >So in the second Query i only want to display items where the >rsStudio.StudioID are not in the list from rsStudioIN.StudioID > >On top of all that, if there is a better way to write this using inner or >left/right joins in one statement that would be cool to see how it works. > >Thanks Again Guys. >-paul > ><CFSET todayDate = #DateFormat(Now())#> > ><cfquery name="rsStudioIN" datasource="kid" dbtype="ODBC"> >SELECT tblKid.StudioID, tblKid.KidDate >FROM tblKid >WHERE Month(tblKid.KidDate) IN (#Month(todayDate)#)</cfquery> > ><cfquery name="rsStudio" datasource="kid"> >SELECT tblStudio.StudioID, tblStudio.StuShort, tblStudio.StudioName >FROM tblStudio ><cfif rsStudioIN.StudioID NEQ ""> >WHERE tblStudio.StudioID NOT IN (#rsStudioIN.StudioID#) ></cfif> >ORDER BY tblStudio.StudioName ASC ></cfquery>
A couple things. First, always scope your variables. It increases readability and is more efficient. Second, you don't need pound signs around CF variables within a CFSET. Third, you're having your problem because you are only referring to the first record in the record set of your first query. You need to use ValueList() to get a list of the values returned from the first query. Here's a solution: <cfset variables.todayDate = DateFormat(Now())> <cfquery name="rsStudioIN" datasource="kid" dbtype="ODBC"> SELECT tblKid.StudioID, tblKid.KidDate FROM tblKid WHERE Month(tblKid.KidDate) IN '#Month(todayDate)#' </cfquery> <cfset variables.studioINList = ValueList(rsStudioIN.StudioID)> <cfquery name="rsStudio" datasource="kid"> SELECT tblStudio.StudioID, tblStudio.StuShort, tblStudio.StudioName FROM tblStudio <cfif ListLen(variables.studioINList)> WHERE tblStudio.StudioID NOT IN (#variables.studioINList#) </cfif> ORDER BY tblStudio.StudioName ASC </cfquery> Hope this helps, Dave. _________________________________________________________________ Send and receive Hotmail on your mobile device: http://mobile.msn.com ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
