Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    21

    Unanswered: SQL Query where all field are not null

    I have the following query:

    Code:
    SELECT
                StudenList.PER_ID,
                PRE.ELA_SCORE AS PRE_ELA,
                PRE.MTH_SCORE AS PRE_MTH,
                POST.ELA_SCALE AS POST_ELA,
                POST.MTH_SCALE AS POST_MTH
    FROM    STUDENTLIST 
    LEFT JOIN POST
        ON 
            ACE.PER_ID                = POST.PER_ID 
            AND POST.YEAR          = '10'
    LEFT JOIN PRE
        ON 
            ACE.PER_ID                = PRE.PER_ID 
            AND PRE.YEAR            = '09'
    It generates the list I need except because there are entries in the file for other subjects, such as social studies, I have rows where all four scores are null.

    What is the most elegant way to filter out where all scores are null? Meaning if there is at least one score, I want the person to show in the list?

    I could use a block of ands which are then or-ed, but there are 16 possible outcomes and I would have to correctly handle all of them. Seems like overkill. Any ideas?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There are indeed 16 combinations, but you want 15 of the them to return, so you only have to handle the one exception.

    where x1 is not null or x2 is not null or x3 is not null or x4 is not null

    Ady

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How about an exists subselect?
    Dave

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another way:
    WHERE COALESCE(x1 , x2 , x3 , x4) IS NOT NULL

Tags for this Thread

Posting Permissions

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