Question 39981 in Mysql Question posted in
The official MySQL documentation can be found here.

NOT Combination with AND – Mysql

I am trying to query a list of rows from the database.
It has 7 fields (columns) (A,B,C,VA,VB,VC,LISTED).

SELECT * FROM datas WHERE 
        ((A=1 OR A IS NULL) AND (B=1 OR B IS NULL) AND (C=1 OR C IS NULL))  -- A/B/C are either 1 or NULL
        AND (VA=1 AND VB=1 AND VC=1)    -- VA/VB/VC must ALL be 1
        AND LISTED=0
        AND NOT (A=1 AND B=1 AND C=1)   -- BUT A/B/C must NOT be ALL 1 (at least one NULL)
ORDER BY ID ASC

Everything is working until i add the AND NOT line. It returns no row.

To explain it shorter, i want to retrieve all rows where A/B/C are either 1 or NULL but not ALL to 1.

And VA/VB/VC must be all to 1. (and LISTED is well 0)

Thanks 🙂

Tags:

2

Answers


  1. 0

    in mysql comparisons return 0 or 1 so they can be summed

    SELECT * FROM datas WHERE 
        ((A=1 OR A IS NULL) AND (B=1 OR B IS NULL) AND (C=1 OR C IS NULL))  -- A/B/C are either 1 or NULL
        AND (VA=1 AND VB=1 AND VC=1)    -- VA/VB/VC must ALL be 1
        AND LISTED=0
        AND ((A IS NULL) +( B IS NULL) + ( C IS NULL)) Between 1 and 2

    — BUT A/B/C must NOT be ALL 1 (at least one NULL)
    ORDER BY ID ASC

    Login or Signup to reply.
  2. 0

    Everything is working until i add the AND NOT line. It returns no row.

    The problem is comparisons against null values in the NOT predicate. A = 1 returns null when A is null, then NOT (NULL) is still NULL. Obviously this is not the behavior you want.

    Instead, you can use <=>, which performs null-safe equality:

    SELECT *
FROM data
WHERE 
    COALESCE(A, 1) = 1 AND COALESCE(B, 1) = 1 AND COALESCE(C, 1) = 1 -- A/B/C are either 1 or NULL
    AND VA = 1 AND VB = 1 AND VC = 1 -- VA/VB/VC must ALL be 1
    AND LISTED=0
    AND NOT (A <=> 1 AND B <=> 1 AND C <=> 1)   -- BUT A/B/C must NOT be ALL 1 (at least one NULL)
ORDER BY ID ASC

    Notes:

    • COALESCE() can be used to shorten the first predicate
    • Parentheses around AND conditions are superfluous (second predicate)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search