CODEWITHSUNDEEP
sql-servert-sql
user1858332
user1858332

Reputation: 2075

Not Equal does not work in SQL Query

enter image description here

I am trying to use the "Not Equal" command in Sql but it does not seem to be working. I want to get the total count when all the 3 fields (status1, status2 and status3) are not equal to Yes. For some reason I am getting 0 records.

SELECT
    COUNT(ID) from [maintable]
WHERE 
    status1 <> 'YES'
    and status2 <> 'YES'
    and status3 <> 'YES'

The above query does not generate any results. What am i doing wrong here? Thanks

Upvotes: 6

Views: 16624

Answers (3)

Jaanu
Jaanu

Reputation: 88

SELECT
    COUNT(ID) from [maintable]
WHERE
    NVL(status1, 'NO') <> 'YES'
    NVL COALESCE(status2, 'NO') <> 'YES'
    NVL COALESCE(status3, 'NO') <> 'YES'

we can use nvl for null cases

Upvotes: 0

Juan Mart&#237;
Juan Mart&#237;

Reputation: 309

Try using EXCEPT.

SELECT
    COUNT(ID) from [maintable]
EXCEPT
SELECT
    COUNT(ID) from [maintable]
WHERE 
    status1 = 'YES'
    and status2 = 'YES'
    and status3 = 'YES'

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460238

Try this:

SELECT
    COUNT(ID) from [maintable]
WHERE
    COALESCE(status1, '') <> 'YES'
    AND COALESCE(status2, '') <> 'YES'
    AND COALESCE(status3, '') <> 'YES'

null values are not <> 'YES', they are undefined.

COALESCE

Upvotes: 15

Related Questions