CODEWITHSUNDEEP
mysqlsql
user1155594
user1155594

Reputation: 315

How to only exclude two matching fields?

I have a table with multiple fields, I want to select all rows where two fields equal a certain value and where field3 is 1 and field4 is 4, 5, 6 or where field3 is 0. However I need to return results when field3 is 0 and field4 is 4, 5, 6. With this query I am not getting rows back with field3 = 0.

Here's my query, 

SELECT * from table where field1 = 1 AND field2 = 0 AND (field3 = 1 AND field4 !=4
AND field4 !=5
AND field4 !=6)

Upvotes: 0

Views: 60

Answers (2)

UweB
UweB

Reputation: 4110

Cluster and bracket your statement exactly according to your prose text, and you're there.

SELECT *
FROM
    table
WHERE
    field1 = x
    AND field2 = y
    AND (
        (field3 = 1 AND field4 IN (4, 5, 6))      -- field3 = 1 and field4 has one of specified values
        OR                                        -- or alternatively
        (field3 = 0 AND field4 NOT IN (4, 5, 6))  -- field3 = 0 and field4 doesn't have any of the specified values
    )

EDIT: Actually - after reading your posting again, you want field4 to be either 4, 5 or 6 and you want field3 to be either 0 or 1 - or am I wrong? This would mean:

SELECT *
FROM
    table
WHERE
    field1 = x
    AND field2 = y
    AND field3 IN (0, 1)
    AND field4 IN (4, 5, 6))

Upvotes: 1

Tobia Zambon
Tobia Zambon

Reputation: 7629

try something like this:

SELECT * from table where 
          field1 = 1 AND field2 = 0 
            AND (
                  (field3 = 1 AND (field4 = 4 OR field4 = 5 OR field4 = 6))
                     OR 
                  (field3 = 0)
                )

Upvotes: 2

Related Questions