Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    22

    Unanswered: Filtering Alias Column

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2011
    Posts
    22
    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.

  4. #4
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Filtering Alias Column

    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

  5. #5
    Join Date
    Sep 2011
    Posts
    22
    Didn't work. It gave me only 2s.

    Quote Originally Posted by homerow View Post
    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

Posting Permissions

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