No. Obviously you need to grab @user_input from the parameter of the report instead of in the way I did it.

Also, I'd test both my solution and Sudhakar's solution. I'm not honestly certain which would be faster.

OUTER APPLY

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;

IN SUBQUERY

SELECT student_id, school_ID

FROM #student_attendance

WHERE student_id IN (SELECT student_id

FROM #student_attendance

WHERE school_ID = @user_input

);

Statistics: -

=================================

OUTER APPLY

=================================

Table '#student_attendance'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

=================================

IN SUBQUERY

=================================

Table '#student_attendance'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

Obviously, on the tiny sample data we have the results are pretty much the same.

Now, I don't know what your actual data looks like, so this might be totally out of whack. But here's some sample data with 1,000,000 rows.

IF object_id('tempdb..#student_attendance') IS NOT NULL

BEGIN

DROP TABLE #student_attendance;

END;

SELECT TOP 1000000 IDENTITY(INT,1,1) AS student_attendance_id,

(ABS(CHECKSUM(NEWID())) % 500000) + 1 AS student_id,

(ABS(CHECKSUM(NEWID())) % 50000) + 1 AS school_ID

INTO #student_attendance

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

Now, if we run the two queries again we get the following: -

=================================

OUTER APPLY

=================================

(70 row(s) affected)

Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 186 ms, elapsed time = 46 ms.

=================================

IN SUBQUERY

=================================

(50 row(s) affected)

Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 125 ms, elapsed time = 34 ms.

Roughly the same over a million rows, with the "IN Subquery" query winning. Please note however, that the "IN Subquery" method produced 50 rows whilst the "OUTER APPLY" produced 70 rows. This is an error in my code. If we correct this error: -

SELECT DISTINCT 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;

Then try again over 1 million rows: -

=================================

OUTER APPLY

=================================

(40 row(s) affected)

Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 140 ms, elapsed time = 36 ms.

=================================

IN SUBQUERY

=================================

(40 row(s) affected)

Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 173 ms, elapsed time = 35 ms.

Each now appears to be identical in both run-time and result-set. Without knowing how your indexes are set on the table, that is the best we're likely to be able to do with helping you.