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
