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

    Unanswered: Filtering Null Values

    I work for a school district and I'm trying to generate a file with the parents contact info in it, as a flat file instead of the way it is stored in our guardian table. Most students don't have guardians associated with them and some have mothers and no father entered and vice versa. So I would like to exclude rows with null values. How would I go about doing that?

    Thanks
    Alex

    Code:
    /****** Script for Guardian Portal Logins  ******/
    SELECT 
           [Student_ID]
          ,isnull([Father_Code],' ') AS Relationship
          ,isnull([Father_First_Name],' ') As First_Name
          ,isnull([Father_Last_Name],' ') AS Last_Name
          ,isnull([Father_HomePhone],' ')AS Home_Phone
          ,isnull([Father_Work_Phone],' ') AS Work_Phone
          ,isnull([Father_Mobile_Phone],' ')AS Mobile_Phone
          ,isnull([Father_Email],' ')AS Email
          FROM [Genesistest].[dbo].[Student_Guardian]
          
          Where School_Year='1112'
          
          Union
          Select 
          [Student_ID]
          ,isnull([Mother_Code],' ')AS Relationship
          ,isnull([Mother_First_Name],' ')AS First_Name
          ,isnull([Mother_Last_Name],' ')AS Last_Name
          ,isnull([Mother_HomePhone],' ')AS Home_Phone
          ,isnull([Mother_Work_Phone],' ')AS Work_Phone
          ,isnull([Mother_Mobile_Phone],' ')AS Mobile_Phone
          ,isnull([Mother_Email],' ')AS Email
          FROM [Genesistest].[dbo].[Student_Guardian]
          
          Where School_Year='1112'
          
          Union
          Select 
          [Student_ID]
          ,isnull([Guardian_Code],' ')AS Relationship
          ,isnull([Guardian_First_Name],' ')AS First_Name
          ,isnull([Guardian_Last_Name],' ')AS Last_Name
          ,isnull([Guardian_HomePhone],' ')AS Home_Phone
          ,isnull([Guardian_Work_Phone],' ')AS Word_Phone
          ,isnull([Guardian_Mobile_Phone],' ')AS Mobile_Phone
          ,isnull([Guardian_Email],' ')AS Email
          
      FROM [Genesistest].[dbo].[Student_Guardian]
      
      Where School_Year='1112'
      
      Order By Student_ID, Relationship

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if you want to compare with NULL you have to use the "is" keyword.

    example:

    select col1,col2,col3 from some_table where some_column is not null

Posting Permissions

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