Indicate a column if it exists in ALL elements of another SQL

-1

I have the following question: "Enter the" name3 "which are in ALL" name ".

And I have 4 tables that are below

-- A (name,Num2, local)
insert into A values ('Favela',1,'WE');
insert into A values ('Lamosa',2,'NA');
insert into A values ('Luz',3,'S0');

-- E (num4,name3,name4,num5)

insert into E values (1,'Teresa','Reino',1);
insert into E values (6,'Maria','Filo',1);
insert into E values (7,'Francisca','Ordem',6);
insert into E values (8,'Rita','Ordem',6);
insert into E values (9,'Beatriz','Ordem',6);
insert into E values (10,'Sofia','Filo',1);
insert into E values (11,'Bea','Ordem',10);

-- C (num5,num3,name2,num4)
insert into C values (2,1,'Cao',9);
insert into C values (1,2,'Gato',8);
insert into C values (1,3,'Golfinho',7);
insert into C values (3,4,'Peixe',11);

 -- B (Num1,name,Num2, Num3) 
insert into B values (1,'Maria',1,1);
insert into B values (2,'Jorge',2,1);
insert into B values (3,'Teresa',2,1);
insert into B values (4,'Rui',3,1);
insert into B values (1,'Fran',3,3);
insert into B values (2,'Juliett',3,3);
    
asked by anonymous 01.12.2016 / 12:41

1 answer

1

You can use the NOT EXISTS clause in WHERE to search for an aquarium without a link:

SELECT cat.nome
  FROM cat_taxonomica cat
 -- Que não tenha um aquário sem vínculo
 WHERE NOT EXISTS(SELECT 1
                    FROM aquario a
                   -- Que não tenha um vínculo com a categoria inicial
                   WHERE NOT EXISTS(SELECT 1
                                      FROM especime esp
                                           INNER JOIN especie e ON e.id_especie = esp.id_especie
                                     WHERE esp.id_aquario = a.id_aquario
                                       AND e.id_cat = cat.id_cat))
  

Subqueries with EXISTS or NOT EXISTS

     

If subquery returns some line, EXISTS is TRUE , and NOT EXISTS is FALSE

We get the result:

 ___________
| nome      |
|___________|
| Carnivora |
|___________|
    
01.12.2016 / 13:17