Why is my query not returning proper results?-CodePudding

I am trying to exclude the records that have channel = all and category = Increase Customer Loyalty. These 2 conditions combined because I have records with this category but different channel. Somewhere in my SQL I am doing something wrong because executing it it excludes all the rows. E.g. all of the records when I expect to see half of them.

SELECT * FROM `flows_predefined`
WHERE platform = 'shopify'
AND status = 1
AND (
    category != 'Increase Customer Loyalty'
    AND channel != 'all'
)
ORDER BY `flows_predefined`.`id` DESC;

CodePudding user response：

The problem is that you want to exclude the records which meet the criteria of

    category = 'Increase Customer Loyalty'
    AND channel = 'all'

but your where looks to the logical expression that the resulting records should meet. So you need to negate this condition:

NOT (
    category = 'Increase Customer Loyalty'
    AND channel = 'all'
}

and now let's apply this to your query:

SELECT * FROM `flows_predefined`
WHERE platform = 'shopify'
AND status = 1
AND NOT (
    category = 'Increase Customer Loyalty'
    AND channel = 'all'
)
ORDER BY `flows_predefined`.`id` DESC;

or, if you prefer the != operand:

SELECT * FROM `flows_predefined`
WHERE platform = 'shopify'
AND status = 1
AND (
    category = 'Increase Customer Loyalty'
    OR channel = 'all'
)
ORDER BY `flows_predefined`.`id` DESC;