Removing all records where one condition is met-CodePudding

I have a table like below:

ID	section	Yes_no
EE_1	sec_21	Y
EE_1	sec_23	N
EE_1	sec_22	N
EE_2	sec_21	N

I'm looking for a way to return ONLY IDs where there is NO 'Y' in the "Yes_no" column. Desired output below:

ID	section	Yes_no
EE_2	sec_21	N

CodePudding user response：

Window functions are usually the fastest for this kind of thing

SELECT
  t.ID,
  t.section,
  t.Yes_no
FROM (
    SELECT *,
      CountY = COUNT(CASE WHEN t.Yes_no = 'Y' THEN 1 END) OVER (PARTITION BY t.ID)
    FROM YourTable t
) t
WHERE CountY = 0;

db<>fiddle

CodePudding user response：

I think this is what you want:

create table MyTable(
  ID varchar(10),
  section varchar(50),
  Yes_no varchar(1)
)
insert into MyTable values 
('EE_1','sec_21','Y'),
('EE_1','sec_23','N'),
('EE_1','sec_22','N'),
('EE_2','sec_21','N')

select *
from MyTable t1
where t1.Yes_no = 'N' and not exists
(select * from MyTable t2 where t2.ID = t1.ID and t2.Yes_no = 'Y')

DBFiddler: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0b24198e227588df323c579facd37153

CodePudding user response：

Select * from table_X where ID not in (Select ID from table_X where Yes_no ='Y')

Running example

CodePudding user response：

select *
from Table t
where t.Yes_no = 'N' and not exists
(select * from Table b where b.ID = b.ID and b.Yes_no = 'Y')