SQL Server 2005 - how to compare field value, and return a count if different, for every occurance

DECLARE @CURRENTSCHOOL TABLE (STUDENT VARCHAR(8), COURSE VARCHAR(8), SCHOOL VARCHAR(2))
INSERT INTO @CURRENTSCHOOL VALUES ('10000000','MCR1010','11')
INSERT INTO @CURRENTSCHOOL VALUES ('12000000','MCR6080','11')
INSERT INTO @CURRENTSCHOOL VALUES ('13000000','MCR6090','15')

DECLARE @OTHERSCHOOLS TABLE (STUDENT VARCHAR(8), COURSE VARCHAR(8), SCHOOL VARCHAR(2))
INSERT INTO @OTHERSCHOOLS VALUES ('10000000','MCR1010','11')
INSERT INTO @OTHERSCHOOLS VALUES ('10000000','MCR1011','14')
INSERT INTO @OTHERSCHOOLS VALUES ('10000000','MCR1012','15')
INSERT INTO @OTHERSCHOOLS VALUES ('12000000','MCR6080','19')
INSERT INTO @OTHERSCHOOLS VALUES ('13000000','MCR6090','15')

For the above sample data. Two tables. Currentschool and Otherschools.

Currentschool is the current course that a student is on including the schoolcode, and is the mai开发者_开发问答n table.

OtherSchools is potentially other courses that a student can go on, in differing schools.

I need to compare the currentschool table against the otherschools table matched using the student id number, and for every different schoolcode in otherschools, it needs to return a count.

eg:

Student:    OtherSchoolCount:
10000000    2        (because of 2 different school codes than than the current school)
12000000    1        (because of 1 different school code than than the current school)
13000000    blank    (because not a different school code)

Is this possible?

Many thanks

M.

SELECT cs.student, 
       COUNT(os.course) 
FROM   @CURRENTSCHOOL cs 
       LEFT JOIN @OTHERSCHOOLS os 
         ON cs.student = os.student 
            AND cs.school <> os.school 
GROUP  BY cs.student 

outputs

STUDENT  
-------- -----------
10000000 2
12000000 1
13000000 0

If Null is really preferred over Zero then you can do this (or use the equivalent CTE)

SELECT student, 
       CASE 
         WHEN coursecount = 0 THEN NULL 
         ELSE coursecount 
       END coursecount 
FROM   (SELECT cs.student, 
               COUNT(os.course) coursecount 
        FROM   @CURRENTSCHOOL cs 
               LEFT JOIN @OTHERSCHOOLS os 
                 ON cs.student = os.student 
                    AND cs.school <> os.school 
        GROUP  BY cs.student) t 

Which outputs

student  courseCount
-------- -----------
10000000 2
12000000 1
13000000 NULL

Update: NullIF could be put to use as alternative to the Case statement see What applications are there for NULLIF()?

select o.Student, count(*) as Count
from CURRENTSCHOOL c1 
inner join OTHERSCHOOLS o on c1.Student = o.Student --this join is to ensure student exists in both tables 
left outer join CURRENTSCHOOL c on o.Student = c.Student
    and o.School= c.School
where c.Student is null
group by o.Student