Conditional JOIN - 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 2008 Forums

Transact-SQL (2008)

Conditional JOIN

Author

Topic

n3xus
Starting Member

6 Posts

Posted - 2011-05-01 : 08:20:00

class_archive

Class_grade | Teacher_ID | Year
10          | t106       | 2010
9           | t105       | 2010

class

Class_grade | Teacher_ID
10          | t108

Settings

Current_School_Year
2011

I need to write a function which would take @Class_grade, @Year and find @TeacherID.

if year is not Current_School_Year it should look For teacher_id in class_archive table.
If year is Current_School_Year it should look For teacher_id in class table.

Following is the logic of statement I'm trying to write.

SELECT student_count.Class_grade, student_count.Year, t.TeacherID  FROM student_count
LEFT OUTER JOIN
	if @year in (SELECT Current_School_Year FROM Settings) THEN class as t
	ELSE (SELECT Class_grade, Teacher_ID FROM class_archive WHERE year = @year) as t END
ON student_count.Class_grade = t.Class_grade

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-01 : 09:32:49

SELECT student_count.Class_grade, student_count.Year, t.TeacherID
FROM student_count
left join
(select teacher_ID, Class_Grade from class_archive where year = @year
union all
select teacher_ID, Class_Grade from class c
where @year = (SELECT Current_School_Year FROM Settings)
) t
on student_count.Class_grade = t.Class_grade

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources