I'm running into a wall with my sql Datasource on the StudentAvail Query. I need to know if the SSNs found in the query are already in MyStudentAssignmentTable this will eventually be rolled in as a sub query to add only those who are not yet in the table. However, If I use the #DSN# as the datasource the #tbl# does not appear and if I use query, MyStudentAssignmentTable does not appear.
Is there a way to do a join on an existing query as well as a table in an existing datasource in the same query or will I need to loop through? Thanks in Advance <cfset dcode = 'AMBL'> <cfset tcode = '#dcode#Codes'> <cfset loopcnt = 4> <cfloop from=1 to=#loopcnt# index='x'> <cfset tMonth = #ListGetAt(ambl, x)#> <cfset M4ctr = 0> <cfquery name="#dcode#R#x#" datasource="#DSN#" > SELECT a.SSN, a.#tMonth#, b.CCF,b.NameDotN,b.LNAME, b.FNAME,b.SSN FROM #M4Scheduletable# a INNER JOIN Studs b ON a.SSN = b.SSN WHERE (b.SSN=b.SSN) And ( <cfloop list="#evaluate(TCode)#" index='y'> <cfset M4Ctr = #M4Ctr#+1> <cfif M4Ctr eq 1> a.#tMonth# = '#y#' <cfelse> OR a.#tMonth# = '#y#' </cfif> </cfloop>) </cfquery> <cfset tbl = '#dcode#R#x#'> <cfquery name="StudentAvail" Datasource="query" debug> SELECT #tbl#.SSN, MyStudentAssignmentTable.StudSSN FROM #tbl# LEFT OUTER JOIN MyStudentAssignmentTable ON MyStudentAssignmentTable.EventItemID = #tID# AND #tbl#.SSN = MyStudentAssignmentTable.StudSSN Where StudSSN is Null </cfquery> </cfloop> David DiPietro Systems Developer / Engineer OSU College of Medicine & Public Health Voice (614) 292-5960 Fax (614) 292-0745 ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.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