Returning Null Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Returning Null Records

Author

Topic

dcummiskey
Starting Member

26 Posts

Posted - 2007-09-17 : 17:37:49

Hello -
I have several tables I'm querying data from:

tblStudents
tblStudentScores
tblLessonPlan
tblTests

tblStudents will always return a record, but tblStudentScores may or
may not return a record. The code snippet below only returns data for students that
have a record in tblStudents and tblStudentScores. Is there a simple way to restructure the code so
that if tblStudentScores does not return a score, it just returns a null
value?

select S.StudentID, S.LastName, S.FirstName, S.Grade, L.Unit, L.TestID, SS.Score, S.SchoolID, S.ClassID, X.ClassName, X.TeacherID, NCES.[School Name], t.Type, t.TestName, t.Book, SS.EvaluationDate, SS.ModifyDate, S.IdNum, 1 as valid,
(select top 1 district from tblUsers where SchoolID = S.SchoolID) as DistrictID
FROM tblStudents S 
Left join tblStudentScores SS on S.StudentID = SS.StudentID 
Left join tblLessonPlan L on SS.LessonPlanID = L.LessonPlanID
Left join tblTests T on L.TestID = T.TestID
Left join NCES..[school data] NCES on NCES.SoprisID = S.SchoolID
Left join tblClasses X on S.ClassID = X.ClassID
where  (SS.Status = 'A')
and S.Status ='A'
and t.testid in (1,2,3,4,5,6,7)
and S.ClassID in (select C.ClassID from tblClasses C left join tblUsers U 
on U.UserID = C.TeacherID where U.SchoolID in (select distinct(schoolid) from tblUsers))

Thanks for your help,
Dan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 17:47:45

[code]select StudentID,
LastName,
FirstName,
Grade,
Unit,
TestID,
Score,
SchoolID,
ClassID,
ClassName,
TeacherID,
[School Name],
Type,
TestName,
Book,
EvaluationDate,
ModifyDate,
IdNum,
valid,
DistrictID
from (
select S.StudentID,
S.LastName,
S.FirstName,
S.Grade,
L.Unit,
L.TestID,
SS.Score,
S.SchoolID,
S.ClassID,
X.ClassName,
X.TeacherID,
NCES.[School Name],
t.Type,
t.TestName,
t.Book,
SS.EvaluationDate,
SS.ModifyDate,
S.IdNum,
1 as valid,
u.district as DistrictID,
row_number() over (partition by u.userid order by ss.modifydate desc) As recid
FROM tblStudents as S
Left join tblStudentScores as SS on S.StudentID = SS.StudentID
and SS.Status = 'A'
Left join tblLessonPlan as L on SS.LessonPlanID = L.LessonPlanID
Left join tblTests as T on L.TestID = T.TestID
and and t.testid in (1, 2, 3, 4, 5, 6, 7)
Left join NCES..[school data] as NCES on NCES.SoprisID = S.SchoolID
Left join tblClasses as X on S.ClassID = X.ClassID
left join tblUsers as u on u.userid = x.teacherid
where S.Status = 'A'
) AS d
where recid = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

dcummiskey
Starting Member

26 Posts

Posted - 2007-09-17 : 17:53:33

Msg 195, Level 15, State 10, Line 42
'row_number' is not a recognized function name.

Just realized I posted in the 2005 t-sql forum...I'm working in 2005 studio but the server is 2000 for this project. Would that cause this error?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 17:59:45

Yes, the probability is very high.

select		S.StudentID,
		S.LastName,
		S.FirstName,
		S.Grade,
		L.Unit,
		L.TestID,
		SS.Score,
		S.SchoolID,
		S.ClassID,
		X.ClassName,
		X.TeacherID,
		NCES.[School Name],
		t.Type,
		t.TestName,
		t.Book,
		SS.EvaluationDate,
		SS.ModifyDate,
		S.IdNum,
		1 as valid,
		(select top 1 district from tblUsers where SchoolID = S.SchoolID) as DistrictID
FROM		tblStudents as S 
Left join	tblStudentScores as SS on S.StudentID = SS.StudentID 
			and SS.Status = 'A'
Left join	tblLessonPlan as L on SS.LessonPlanID = L.LessonPlanID
Left join	tblTests as T on L.TestID = T.TestID
			and t.testid in (1, 2, 3, 4, 5, 6, 7)
Left join	NCES..[school data] as NCES on NCES.SoprisID = S.SchoolID
Left join	tblClasses as X on S.ClassID = X.ClassID
where		S.Status ='A'
		and exists (select * from tblUsers as U where U.SchoolID is not null and S.teacherid = u.userid)

E 12°55'05.25"
N 56°04'39.16"

dcummiskey
Starting Member

26 Posts

Posted - 2007-09-17 : 18:24:05

How did you do that so fast? :D It's mostly working, except for the columns:
L.Unit,
L.TestID,
t.Type,
t.TestName,
t.Book,

Is it possible to return those values if the SS.Score value is null? They come back as nulls right now.

I modified the code a little to loop through the testids in the lessonplan table.


select		S.StudentID,
		S.LastName,
		S.FirstName,
		S.Grade,
		L.Unit,
		L.TestID,
		SS.Score,
		S.SchoolID,
		S.ClassID,
		X.ClassName,
		X.TeacherID,
		NCES.[School Name],
		t.Type,
		t.TestName,
		t.Book,
		SS.EvaluationDate,
		SS.ModifyDate,
		S.IdNum,
		1 as valid,
		(select top 1 district from tblUsers where SchoolID = S.SchoolID) as DistrictID
FROM		tblStudents as S 

Left join	tblStudentScores as SS on S.StudentID = SS.StudentID 
			and SS.Status = 'A'

Left join	tblLessonPlan as L on SS.LessonPlanID = L.LessonPlanID
			and l.lessonplanid in (SELECT lessonplanid FROM tblLessonPlan where testid in (1,2,3,4,5,6,7))

Left join	tblTests as T on L.TestID = T.TestID
			--and t.testid in (1, 2, 3, 4, 5, 6, 7)

Left join	NCES..[school data] as NCES on NCES.SoprisID = S.SchoolID

Left join	tblClasses as X on S.ClassID = X.ClassID

where		S.Status ='A'
		and exists (select * from tblUsers as U where U.SchoolID is not null AND u.SCHOOLID IN (select distinct(schoolid) from tblUsers where district = '1201080') and X.teacherid = u.userid)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 18:52:24

You are using overextended IN statements.

This

Left join	tblLessonPlan as L on SS.LessonPlanID = L.LessonPlanID
			and l.lessonplanid in (SELECT lessonplanid FROM tblLessonPlan where testid in (1,2,3,4,5,6,7))

is exaxtly the same as

Left join	tblLessonPlan as L on SS.LessonPlanID = L.LessonPlanID
			and l.testid in (1, 2, 3, 4, 5, 6, 7)

The same thing goes for your double-in subquery in the where part...

So the query looks like

select		S.StudentID,
		S.LastName,
		S.FirstName,
		S.Grade,
		L.Unit,
		L.TestID,
		SS.Score,
		S.SchoolID,
		S.ClassID,
		X.ClassName,
		X.TeacherID,
		NCES.[School Name],
		t.Type,
		t.TestName,
		t.Book,
		SS.EvaluationDate,
		SS.ModifyDate,
		S.IdNum,
		1 as valid,
		(select top 1 district from tblUsers where SchoolID = S.SchoolID) as DistrictID
FROM		tblStudents as S 
Left join	tblStudentScores as SS on S.StudentID = SS.StudentID 
			and SS.Status = 'A'
Left join	tblLessonPlan as L on SS.LessonPlanID = L.LessonPlanID
			and l.testid in (1, 2, 3, 4, 5, 6, 7)
Left join	tblTests as T on L.TestID = T.TestID
Left join	NCES..[school data] as NCES on NCES.SoprisID = S.SchoolID
Left join	tblClasses as X on S.ClassID = X.ClassID
where		S.Status ='A'
		and exists (select * from tblUsers as U where U.SchoolID is not null and u.district = '1201080' and x.teacherid = u.userid)

E 12°55'05.25"
N 56°04'39.16"

dcummiskey
Starting Member

26 Posts

Posted - 2007-09-17 : 18:59:54

Ah, got it. I've been using that IN statement by habit..thanks for the tip. This works, I should be able to handle the nulls on the reporting side of things. Appreciate the help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-17 : 19:04:44

This might work too

select		S.StudentID,
		S.LastName,
		S.FirstName,
		S.Grade,
		L.Unit,
		L.TestID,
		SS.Score,
		S.SchoolID,
		S.ClassID,
		X.ClassName,
		X.TeacherID,
		NCES.[School Name],
		t.Type,
		t.TestName,
		t.Book,
		SS.EvaluationDate,
		SS.ModifyDate,
		S.IdNum,
		1 as valid,
		u.district as DistrictID
FROM		tblStudents as S 
Left join	tblStudentScores as SS on S.StudentID = SS.StudentID 
			and SS.Status = 'A'
Left join	tblLessonPlan as L on SS.LessonPlanID = L.LessonPlanID
			and l.testid in (1, 2, 3, 4, 5, 6, 7)
Left join	tblTests as T on L.TestID = T.TestID
Left join	NCES..[school data] as NCES on NCES.SoprisID = S.SchoolID
Left join	tblClasses as X on S.ClassID = X.ClassID
left join	tblUsers as U on x.teacherid = u.userid
			and u.district = '1201080'
where		S.Status ='A'
		and u.userid is not null

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources