Twisted SQL statement

[tziviak]

I need a sql statement that shows-the child's session information(ex. date, time started, time ended), therapists info(ex. name, id), and child's info (ex. id,name, dob)
But to make it tougher-I need all the sessions per child-with the specified child, and therapist-but the query should display -all the child's sessions-for the day that the specified therapist saw the child (but should include all the sessions-including other therapist's session-for that day, that child)
ex. if therapist A saw child Z on 1/1/03 (and therapist B,D,& F saw child Z on 1/1/03 too) then the query should display all 4 session for therapist a,b,d,& f)

This is the code to get the specific therapist and child-but not the other therapists info:


sql = &quot;SELECT Sessions.UserName, Sessions.OSIS, Sessions.WereServiceRendered, Sessions.ServiceType, Sessions.ErrorCodes, Sessions.DateOfService, Sessions.TimeSessionStarted,Sessions.TimeSessionEnded, Sessions.TotalSessionDuration, Students.[Last Name], Students.[First Name], Students.[Date of Birth], Employees.[LAST NAME], Employees.[FIRST NAME], Employees.[Employee ID] FROM (Sessions INNER JOIN Students ON Sessions.OSIS = Students.[Student ID]) INNER JOIN Employees ON Sessions.EmployeeID = Employees.[Employee ID] WHERE (((Sessions.DateOfService)>= #&quot; & BeginDate & &quot;#) AND ((Sessions.DateOfService) <= #&quot; & EndDate & &quot;#)) &quot;

sql = sql & &quot; GROUP BY Sessions.UserName, Sessions.OSIS, Sessions.WereServiceRendered, Sessions.ServiceType, Sessions.DateOfService, Sessions.TimeSessionStarted,Sessions.TimeSessionEnded, Sessions.TotalSessionDuration,Students.[Last Name], Students.[First Name], Students.[Date of Birth], Employees.[LAST NAME], Employees.[FIRST NAME], Employees.[Employee ID], Sessions.ErrorCodes HAVING ( ((Sessions.WereServiceRendered)='y') AND (Sessions.ErrorCodes Is Null Or Sessions.ErrorCodes <>'del') ) &quot;
if StudentId <> &quot;&quot; then sql = sql & &quot; and Sessions.OSIS = '&quot; & StudentId & &quot;'&quot;
sql = sql & &quot; and Employees.[Employee ID] =&quot; & Session(&quot;EmpID&quot & &quot; order by Sessions.DateOfService, Sessions.TimeSessionStarted &quot;%>

Any Help would be appreciated.

 

[BillDHS]

I didn't get into a lot of detail, but I see a self join.

First create a derived, temp, or permanent table with the child id and service date. Then join that table back to the main table on child and service date to get the therapist and supplemental data.

This should get you all the therapists who saw that child on the same date.

 

[vtj]

You can also use a subselect in your where clause.

where
childid in (select a.childid from maintable a
where a.therapist = therapistid)

VTJ