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 */

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

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.