Date: 01/17/06 (SQL Server)    Keywords: no keywords

I'm trying to do this in ONE query - although I'm not sure that's possible.

Basically, I need a list of children ordered by the center and classroom they were enrolled in as of a certain date. Current center and classroom data is stored in the child table with the child's info but if/when they are transferred, the info about their old center, old classroom, new center and new classroom is stored in the transfers table. Any ideas on how to combine this into ONE big query?

(I like the way a recent poster formatted their question. I'll try to emulate theirs in hopes of giving the most info.)

child
- ID
- first
- last
- dob
- enrollment_date
- drop_date
- center
- classroom

eligibility
- child_ID
- income_eligible

centers
- ID
- name

classrooms
- ID
- name

transfers
- ID
- child_id
- old_center
- old_classroom
- new_center
- new_classroom
- transfer_date

This query gives me the data I need for each child, ordered by Center (school) and Classroom:

SELECT child.ID AS 'ChildID', child.first + ' ' + child.last AS 'Child Name', child.dob AS 'Date of Birth', child.enrollment_date AS 'Enrollment Date', centers.name AS 'Center', classrooms.name AS 'Classroom', eligibility.income_eligible AS 'Income Eligible at Enrollment?' FROM child INNER JOIN eligibility ON child.ID = eligibility.child_id INNER JOIN centers ON child.center = centers.ID INNER JOIN classrooms ON child.classroom = classrooms.ID WHERE child.enrollment_date <= '10/31/2005' AND (child.drop_date is NULL OR child.drop_date > '10/31/2005') ORDER BY child.center, child.classroom, child.last, child.first

SELECT child_id, old_center, old_classroom, new_center, new_classroom, transfer_date FROM transfers WHERE transfer_date > '10/31/2005'

Source: http://www.livejournal.com/community/sqlserver/40505.html