Filtering Alias Column

acidburn85 · #1 (**permalink**) 01-24-12, 15:31

I have a copy of class schedules with only students that are taking half of a full year class in a separate table. The table lists the term that the students are taking so I joined that table to the actual class schedule table via the code below. The values are 1 & 2 and if it's null (not taking half of a full year class) it's a 9. So now I only need 9s and 2s to bell pulled from the script below. How do I go about doing that since HLF_Term is not a real column?

Code:

SELECT STUSCHEDULE.[School_Year]
      ,STUSCHEDULE.[School_Number]
      ,STUSCHEDULE.[Student_ID]
      ,STUSCHEDULE.[CourseID]
      ,STUSCHEDULE.[Section]
      ,STUSCHEDULE.[Term]
      ,isnull(StuHalfYear.[Term],'9') AS HLF_Term
      
      
  FROM [GSchool].[dbo].[StuHalfYear] Right Join
  [GSchool].[dbo].[STUSCHEDULE]
  
  ON STUSCHEDULE.Student_Id=StuHalfYear.Student_ID AND 
	 STUSCHEDULE.CourseId=StuHalfYear.CourseID AND
	 STUSCHEDULE.Section=StuHalfYear.Section
	 
	 Where STUSCHEDULE.School_Year='1112' AND
			Stuschedule.School_Number='0021' 
 
			
  
  ORDER By HLF_Term DESC

gvee · #2 (**permalink**) 01-24-12, 15:37

Use a subquery approach:

Code:

SELECT your
     , columns
     , and
     , aliased_column
FROM  (
       SELECT your
            , columns
            , and
            , an As aliased_column
       FROM   your_table
        INNER
         JOIN etc ....
       ) As subquery
WHERE  aliased_column = value

acidburn85 · #3 (**permalink**) 01-24-12, 15:55

I think I'm just looking at this too hard. It looks simple but why are there two duplicate selects in your example? Just trying to understand how to apply that to my code.

homerow · #4 (**permalink**) 01-24-12, 19:08

Might the following be an alternative (though not the most efficient as the table grows)?

Code:

SELECT
  STUSCHEDULE.[School_Year]
 ,STUSCHEDULE.[School_Number]
 ,STUSCHEDULE.[Student_ID]
 ,STUSCHEDULE.[CourseID]
 ,STUSCHEDULE.[Section]
 ,STUSCHEDULE.[Term]
 ,ISNULL(StuHalfYear.[Term],'9') AS HLF_Term
FROM
  [GSchool].[dbo].[StuHalfYear]
 ,[GSchool].[dbo].[STUSCHEDULE]
WHERE STUSCHEDULE.School_Year = '1112'
AND STUSCHEDULE.School_Number = '0021'
AND STUSCHEDULE.Student_Id = StuHalfYear.Student_ID
AND STUSCHEDULE.CourseId = StuHalfYear.CourseID
AND STUSCHEDULE.Section = StuHalfYear.Section
AND ISNULL(StuHalfYear.[Term],'9') IN ('9','2') -- << 9s and 2s >>
ORDER By HLF_Term DESC

acidburn85 · #5 (**permalink**) 01-25-12, 08:09

Didn't work. It gave me only 2s.

Quote:

Originally Posted by homerow

Might the following be an alternative (though not the most efficient as the table grows)?

Code:

SELECT
  STUSCHEDULE.[School_Year]
 ,STUSCHEDULE.[School_Number]
 ,STUSCHEDULE.[Student_ID]
 ,STUSCHEDULE.[CourseID]
 ,STUSCHEDULE.[Section]
 ,STUSCHEDULE.[Term]
 ,ISNULL(StuHalfYear.[Term],'9') AS HLF_Term
FROM
  [GSchool].[dbo].[StuHalfYear]
 ,[GSchool].[dbo].[STUSCHEDULE]
WHERE STUSCHEDULE.School_Year = '1112'
AND STUSCHEDULE.School_Number = '0021'
AND STUSCHEDULE.Student_Id = StuHalfYear.Student_ID
AND STUSCHEDULE.CourseId = StuHalfYear.CourseID
AND STUSCHEDULE.Section = StuHalfYear.Section
AND ISNULL(StuHalfYear.[Term],'9') IN ('9','2') -- << 9s and 2s >>
ORDER By HLF_Term DESC

Internet Services

Web Development

Digital Marketing

Consumer Tech