Display unique column when not all values are distinct
If I understand your question correctly, I think NOT EXISTS should work.

DECLARE @T TABLE (
    NAME VARCHAR(30)
    ,STATUS VARCHAR(5)
    ,Contact VARCHAR(5)
    ,EpisodeNumber INT
    );
INSERT INTO @T
VALUES
    ('AA', 'C', 'E', 1),
    ('AA', 'C', 'F', 1),
    ('AA', 'C', 'G', 1),
    ('AA', 'D', 'H', 1),
    ('BB', 'D', 'I', 2),
    ('CC', 'C', 'J', 3),
    ('DD', 'C', 'K', 4)
;
SELECT *
FROM @T t
WHERE NOT EXISTS (
        SELECT *
        FROM @T
        WHERE NAME = t.NAME
            AND EpisodeNumber = t.EpisodeNumber
            AND STATUS = 'D'
        );




| NAME | STATUS | Contact | EpisodeNumber |
|------|--------|---------|---------------|
| CC   | C      | J       | 3             |
| DD   | C      | K       | 4             |