Three-table join with NO EXISTS

I have three tables and would like to answer the following question in SQL:

“Who has only certifications that do NOT have scores?”

For instance, in the setup below, the query would return “John” only. Joana has the “AWS Certification”, which is in SCORE table (id 57). Marry has the “ITIL V3 Certification” which is not in the SCORE table, but she also has “Professional Python Dev”, which is in thee SCORE table.

PERSON
| PERSON_ID | PERSON_NAME |
| --------  | -------------- |
| 12    | John            |
| 23   | Mary            |
| 24   | Joana            |


**CERTIFICATION**
|CERTIFICATION_ID| CERTIFICATION_NAME | PERSON_ID|
| -------- |----------- | -------------- |
| 53 | Java Certification    |    12       |
| 54 | ITIL V3 Certification    |    23       |
| 55 | Professional Python Dev            | 23|
| 56 |GCP Certification            |23   | 
| 57 |AWS Certification            |24   | 

SCORES

|SCORE_ID| CERFITICATION_ID | SCORE_DETAILS |
| -------- |----------- | -------------- |
| 70 |55   | 80%            |
| 71 |56   | 90%            |
| 72 |57   | 95%           |

I am trying to implement this in SQL only without having to iterate over the records and without using stored procedures.

SQL for creating these tables and adding the data in case anyone needs it:

create table person(
person_id integer,
person_name varchar(16)
);

create table certification(
CERTIFICATION_ID int,
  CERTIFICATION_NAME varchar(16),
  person_id int
);

create table scores(
  SCORE_ID int,
  CERTIFICATION_ID int,
 SCORE_DETAILS varchar(16));


insert into person(person_id, person_name) values(12, 'John');
insert into person(person_id, person_name) values(23, 'Mary');
insert into person(person_id, person_name) values(24, 'Joana');


insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(53,'A', 12);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(54,'B',23);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(55,'C', 23);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(56,'D', 23);
insert into certification(CERTIFICATION_ID,CERTIFICATION_NAME,person_id) values(57,'E', 24);

insert into scores (SCORE_ID,CERTIFICATION_ID, SCORE_DETAILS) values (70,55,'e');
insert into scores (SCORE_ID,CERTIFICATION_ID, SCORE_DETAILS) values (71,56,'f');
insert into scores (SCORE_ID,CERTIFICATION_ID, SCORE_DETAILS) values (72,57,'g');

Answer

You can use following query

select *
  from person p
 where not exists (select 1
                 from certification c
                 join scores s on s.certification_id = c.certification_id
                where p.person_id = c.person_id
              )

Advertisement

Answer