SQL Query where all field are not null

jkuyken · #1 (**permalink**) 06-08-10, 15:26

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?

ARWinner · #2 (**permalink**) 06-08-10, 15:34

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

dav1mo · #3 (**permalink**) 06-08-10, 15:48

How about an exists subselect?
Dave

tonkuma · #4 (**permalink**) 06-08-10, 22:03

Another way:
WHERE COALESCE(x1 , x2 , x3 , x4) IS NOT NULL

Internet Services

Web Development

Digital Marketing

Consumer Tech