skizzly (9/18/2012)
Hi All,new here so be kind....:)
I have a task to identify all students in a dataset of 49K that have multiple open attendances at schools.
I can pull all students that have multiple open attendances and produce a list of these:
SELECT studentID
FROM step1 [ step1 identifies all student with open attendances ]
GROUP BY studentID
HAVING (COUNT(*) > 1)
The help i need is how to then enable end user to specify a particular school and return all students with open attendance at that specified school AND show the other schools the student has a linked open attendance with.
Example:
studentID, schoolID
1 2
1 3
2 3
3 4
4 5
user selects schoolID of 3, then returned set would show:
schoolId, studentId
3 1
2 1
Thanks very much for any help that can be passed my way!
Here's the correct way to lay out sample data: -
CREATE TABLE #student_attendance (student_attendance_id INT IDENTITY(1,1), student_id INT, school_ID INT);
INSERT INTO #student_attendance(student_id, school_ID)
SELECT student_id, school_ID
FROM (VALUES(1,2),(1,3),(2,3),(3,4),(4,5))a(student_id, school_ID);
Really, you should have set it up like that for us, so that anyone wanting to help you can copy it straight into SSMS then execute and have a replica of your table. This helps you to get tested and working code from anyone wanting to help.
OK, using the sample data above, here is one way to solve the question asked: -
DECLARE @user_input INT = 3;
SELECT final_result_set.student_id, final_result_set.school_ID
FROM (SELECT student_id, school_ID
FROM #student_attendance
WHERE school_ID = @user_input) school_filter(student_id, school_ID)
OUTER APPLY (SELECT student_id, school_ID
FROM #student_attendance
WHERE student_id = school_filter.student_id
AND school_ID <> school_filter.school_ID) other_schools(student_id, school_ID)
OUTER APPLY (SELECT school_filter.student_id, school_filter.school_ID
UNION SELECT other_schools.student_id, other_schools.school_ID
WHERE other_schools.student_id IS NOT NULL) final_result_set;
Results in: -
student_id school_ID
----------- -----------
1 2
1 3
2 3