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

Asked

Viewed 42 times

-1

I have the following question : "Enter the "name3" that 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);
mysql sql

  • Can’t post the table structure with the data? It’s really hard to transpose all tables by images.

  • 2

    Posting images instead of writing in the post is not a good idea. Take a look at how to create a [mcve], in [Ask] and on Manual on how NOT to ask questions.

  • I’ve already edited the question

1 answer

1


You can use the clause NOT EXISTS in the WHERE to look for an unconnected aquarium:

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 the subquery return some line, EXISTS is TRUE, and NOT EXISTS is FALSE

We get the result:

 ___________
| nome      |
|___________|
| Carnivora |
|___________|

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.