How to delete by combining values with the same department_id?

Transact-SQL
1
nature protection department_Id
Environmental Police 10
Guara Wolf 10
Lear's Macaw 10
Australian Koala 10
animal Ongs 1
illegal loggers 1
animal trafficker 0
illegal loggers 0
illegal loggers 2
animal trafficker 2

  I want to delete all lines of the same department_id that contain illegal nature and animal nature together with traffic protection, if the animal nature contains the protection Ongs have to preserve illegal loggers e protection Ongs with the same department_Id. Remembering that I don't know how to combine the same department_id of the two different protections, I can't put it in the loggers function, how do I do it?

DELETE FROM table WHERE (nature = 'illegal' and nature = 'animal' and protection = 'trafficker') and department_id = department_id

The result must be this:

nature protection department_Id
Environmental Police 10
Guara Wolf 10
Lear's Macaw 10
Australian Koala 10
animal Ongs 1
illegal loggers 1
2 

DROP TABLE IF EXISTS #data; /* just data setup part */
CREATE TABLE #data ( nature varchar(30) NOT NULL, protection varchar(30) NOT NULL, department_id int NOT NULL )
INSERT INTO #data VALUES
    ('Environmental', 'Police', 10), ('Guara', 'Wolf', 10),  ('Lear''s', 'Macaw', 10),  ('Australian', 'Koala', 10),
    ('animal', 'Ongs', 1), ('illegal', 'loggers', 1),
    ('animal', 'trafficker', 0), ('illegal', 'loggers', 0),
    ('illegal', 'loggers', 2), ('animal', 'trafficker', 2)
SELECT 'original', * FROM #data ORDER BY department_id, nature, protection

DELETE FROM #data /*!! this is the actual code !!*/
WHERE department_id IN (
    SELECT department_id
    FROM #data
    GROUP BY department_id
    HAVING MAX(CASE WHEN nature = 'illegal' THEN 1 ELSE 0 END) = 1 AND 
        MAX(CASE WHEN nature = 'animal' AND protection = 'trafficker' THEN 1 ELSE 0 END) = 1 AND
        MAX(CASE WHEN nature = 'animal' AND protection = 'Ongs' THEN 1 ELSE 0 END) = 0
)

SELECT 'after_delete', * FROM #data ORDER BY department_id, nature, protection
1 Like