Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    40

    Unanswered: Conditional Join

    class_archive
    Code:
    Class_grade | Teacher_ID | Year
    10          | t106       | 2010
    9           | t105       | 2010
    class
    Code:
    Class_grade | Teacher_ID
    10          | t108
    Settings
    Code:
    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.
    Code:
    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this
    Code:
    SELECT student_count.Class_grade, 
    	student_count.Year, 
    	t.TeacherID  
    FROM student_count
    	LEFT OUTER JOIN
    	(SELECT Class_grade, Teacher_ID, Settings.Current_School_Year AS TheYear
    	FROM class, Settings
    	WHERE Class_grade = @Class_grade and
    		Settings.Current_School_Year = @Year
    	UNION
    	SELECT Class_grade, Teacher_ID, Year AS TheYear
    	FROM class_archive, Settings
    	WHERE Class_grade = @Class_grade AND
    		Year = @Year
    	) as T ON
    		student_count.Class_grade = @Class_grade AND
    		student_count.Year = @Year
    Try to avoid using reserved words in the naming of your database objects, like the use of "Year". Call it ScholasticYear or something like that.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Mar 2008
    Posts
    40
    Thank You so much wim. UNION is working.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    If you went through the code, you must have noticed that the LEFT OUTER JOIN condition (... ON student_count.Class_grade = @Class_grade AND student_count.Year = @Year) was superfluous. This is even simpler:
    Code:
    SELECT Class_grade, Teacher_ID, Settings.Current_School_Year AS TheYear
    FROM class, Settings
    WHERE Class_grade = @Class_grade and
    	Settings.Current_School_Year = @Year
    UNION
    SELECT Class_grade, Teacher_ID, Year AS TheYear
    FROM class_archive, Settings
    WHERE Class_grade = @Class_grade AND
    	Year = @Year
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Mar 2008
    Posts
    40
    overachiever

    Yeah. Your query logic is better than mine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •