Geek asks and answers

SQL Server 2005 - how to compare a field value and return a counter, if different, for each event

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 other schools.

Currentschool is the current course in which the student includes the school code and is the main table.

OtherSchools are potentially other courses a student can continue in different schools.

I need to compare the currentschool table with the otherchools table, matched using the student number, and for every other school code in other schools, he needs to return the bill.

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 it possible?

Many thanks

M.

+3
source share
2 answers
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

exits

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

Null Zero, ( 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 


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

: NullIF Case, . NULLIF()?

+3
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
+3

More articles:


All Articles