Afternoon Paul,
What you need to do is create a value list from the StudioID column and use
that in your next query, like this....
<cfset StudioIDlist = ValueList(reStudioIN.StudioID)>
<cfquery name="rsStudio" datasource="kid">
SELECT tblStudio.StudioID, tblStudio.StuShort, tblStudio.StudioName
FROM tblStudio
<cfif listlen(StudionList)>
WHERE tblStudio.StudioID NOT IN (#StudioIDList#)
</cfif>
ORDER BY tblStudio.StudioName ASC
</cfquery>
Incidentally you can probably do this :
<cfquery name="rsStudio" datasource="kid">
SELECT tblStudio.StudioID, tblStudio.StuShort, tblStudio.StudioName
FROM tblStudio
WHERE tblStudio.StudioID
NOT IN (SELECT StudioID
FROM tblKid
WHERE Month(KidDate) IN (#Month(todayDate)#))
ORDER BY tblStudio.StudioName ASC
</cfquery>
I suspect that there is a JOIN of some kind, but its too warm in the office
and my brain isn't working quite right this afternoon... ;o)
Hope that helps
Regards
Stephen
----- Original Message -----
From: "Paul Ihrig" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, April 09, 2002 2:01 PM
Subject: Better way? Loop, Sql help
> 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>
>
>
______________________________________________________________________
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
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
