Where clause to include and exclude sql

Question

I have two tables where I am selecting values and then filtering them so that:

1. BookId would be 11 or 12
2. C.Id is not 5515, 7582, 7648
3. C.Id can be 5967 , but if D.part is equal to 8, then do not inlude row which has C.Id=5967 and D.part = 8

This is my select:

select * from dbo.C c
cross join dbo.D d 
where
c.Id = d.Id 
AND (c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
and (c.Id not in (5515, 7582, 7648) -- 5515, 7582, 7648 (do not include)
or (c.Id = 5967 AND d.part <> 8)) -- include 5967 but not with part = 8

it all works fine except the last line `(c.Id = 5967 AND d.part <> 8), I still see this row in my results. What I'm doing wrong here?

Answer 1

I interpret the sentence: "D.part is equal to 8, then do not include row which has C.Id=5967" as:

(D.part = 8) => (C.Id <> 5967)

This is logically equivalent with:

NOT (D.part = 8) OR (C.Id <> 5967)
(D.part <> 8) OR (C.Id <> 5967)

This can be rewritten as:

NOT (D.part = 8 AND C.Id = 5967)

Your complete query would then be:

SELECT * 
FROM dbo.C c
CROSS JOIN dbo.D d 
WHERE c.Id = d.Id 
AND (c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
AND (c.Id not in (5515, 7582, 7648) -- 5515, 7582, 7648 (do not include)
AND NOT (D.part = 8 AND C.Id = 5967) -- include 5967 but not with part = 8

I would suggest rewriting the query with a JOIN instead of CARTESIAN PRODUCT

SELECT * 
FROM dbo.C c
JOIN dbo.D d 
    ON c.Id = d.Id 
WHERE (c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
AND (c.Id not in (5515, 7582, 7648) -- 5515, 7582, 7648 (do not include)
AND NOT (D.part = 8 AND C.Id = 5967) -- include 5967 but not with part = 8

Answer 2

With this specific part of the condition:

(c.Id not in (5515, 7582, 7648)  or  (c.Id = 5967 AND d.part <> 8))

a row where c.Id is 5967 and d.part 8 will be evaluated like this

(c.Id not in (5515, 7582, 7648)  or  (c.Id = 5967 AND d.part <> 8))
                 ↓                                 ↓
               true              or              false         →      true

So you can see why this row would be returned.

I can see more than one way to change your condition. Here is one option:

c.Id = d.Id 
AND (c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
and (c.Id not in (5515, 7582, 7648)) -- 5515, 7582, 7648 (do not include)
and (c.Id <> 5967 or d.part <> 8) -- exclude 5967 too unless part <> 8

Here is another:

c.Id = d.Id 
AND (c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
and (c.Id not in (5515, 7582, 7648, 5967) -- 5515, 7582, 7648, 5967 (do not include)
  or (c.Id = 5967 and d.part <> 8)) -- include 5967 but not with part = 8

The second one is almost identical to yours. The only difference is that 5967 is included in the in list. It might seem redundant to list the same value in multiple predicates, though the way the logic is expressed I believe it is perfectly fine. Still, the first option has no such repetition in case that is your preference, and the results produced would be the same.

On a different note, please consider taking full advantage of the explicit JOIN syntax to separate different kinds of conditions. The c.Id = d.Id part is a joining condition while the others are filtering conditions. A joining condition would make most sense in the ON clause. So instead of CROSS JOIN ... WHERE <joining_condition>, use INNER JOIN ... ON <joining_condition>, like this:

select *
from dbo.C c
inner join dbo.D d on c.Id = d.Id  -- joining condition, moved from WHERE
where  -- filtering conditions go here
(c.BookId = 11 or c.BookId = 12) -- BookId is 11 or 12
and ... /* the rest of the conditions */