sql query to exclude results if criteria is met

i have a query which selects name, appointment date and their status icon. I want to exclude any records for the person if they have a status icon of 3. Such as below:

name	appt date	status icon
bob	11/11/2021	1
bob	11/12/2021	2
jane	11/12/2021	1
jane	11/12/2021	3
jane	11/13/2021	4
tim	11/11/2021	1
tim	11/11/2021	2
tim	11/11/2021	3
karen	11/11/2021	2

If i use where status icon <> ‘3’, it still shows jane and tim. I want the query to only return bob and karen, as they don’t have status icon values of 3.

Answer

You’re looking for NOT EXISTS:

DECLARE @t1 TABLE (name VARCHAR(50), appt_date DATE, status_icon INT)
INSERT INTO @t1
VALUES
('bob','11/11/2021',    1),
('bob','11/12/2021',    2),
('jane','11/12/2021',   1),
('jane','11/12/2021',   3),
('jane','11/13/2021',   4),
('tim','11/11/2021',    1),
('tim','11/11/2021',    2),
('tim','11/11/2021',    3),
('karen','11/11/2021',  2)

SELECT t.name, t.appt_date, t.status_icon
FROM @t1 t
WHERE NOT EXISTS (SELECT 1 FROM @t1 te WHERE t.name = te.name AND te.status_icon = 3)

name	appt_date	status_icon
bob	2021-11-11	1
bob	2021-11-12	2
karen	2021-11-11	2