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:

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.

CodePudding user response：

SELECT A.name, A.appt_date, A.status_icon
FROM Table A
WHERE A.name not in (SELECT A1.name FROM Table A1 where A1.status_icon = 3)

What dogyog has suggested is also right but If you specifically want to exclude those names then you can use this query. Works perfectly fine.

CodePudding user response：

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)