DB: Oracle 19c
Client: SQL Developer
I wouldn't be able to post the real data and hence this sample data:
create table customer
(
id NUMBER,
name VARCHAR(50),
result VARCHAR(50),
PRIMARY KEY(id)
);
insert into customer values (1, 'John', 'Match');
insert into customer values (2, 'Jay', 'Mismatch');
insert into customer values (3, 'Mike', 'Match');
insert into customer values (4, 'Nancy', 'Match');
insert into customer values (5, 'Ajit', 'Match');
insert into customer values (6, 'Ram', 'Mismatch');
insert into customer values (7, 'Raj', 'Match');
insert into customer values (8, 'Bob', 'Match');
insert into customer values (9, 'Sam', 'Match');
insert into customer values (10, 'John', 'Match');
insert into customer values (11, 'Mike', 'Mismatch');
insert into customer values (12, 'Nancy', 'Match');
insert into customer values (13, 'Ajit', 'Match');
insert into customer values (14, 'Ram', 'Mismatch');
insert into customer values (15, 'Raj', 'Match');
insert into customer values (16, 'Bob', 'Match');
insert into customer values (17, 'Sam', 'Match');
insert into customer values (18, 'Nancy', 'Match');
insert into customer values (19, 'Ajit', 'Match');
insert into customer values (20, 'Ram', 'Match');
insert into customer values (21, 'Raj', 'Match');
insert into customer values (22, 'Bob', 'Mismatch');
insert into customer values (23, 'Sam', 'Match');
insert into customer values (24, 'John', 'Match');
insert into customer values (25, 'Jay', 'Mismatch');
I am trying to list the ID and NAME of those who never had 'Match' as Result.
The query I tried is as below-
select id, name
from customer
where result = 'Match'
and id in
(select id
from customer
where result = 'Mismatch')
group by id, name having count(name) = 0;
Appreciate if I could get suggestions to list ID and NAME of those who never had a result = ‘Match’