Get elements from one dependent column of another

Asked

Viewed 213 times

1

I have to make a table where indicate the "name2", "local" and "name" of the animals in greater number in each "name".

SELECT DISTINCT OP.nome , OP.localizacao , AD.nome_cientifico   
FROM  C AD , A OP, B CP
WHERE OP.id_aquario = CP.id_aquario AND 
      CP.id_especie = AD.id_especie AND
      NOT EXISTS(SELECT *
                 FROM  C AD2
                 WHERE AD2.id_aquario = CP.id_aquario) 
GROUP BY OP.nome;

Tables 

-- 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');

-- 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);

-- 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);
mysql sql
  • 1

    You have to at least put the names of the columns of the tables, otherwise it is very difficult to imagine which structure

  • You’re right , my mistake , He missed this part

  • Search for Mysql LIMIT usage.

1 answer

2


You can use a subquery to obtain the quantity of each species per tank. However, to show only one record per tank you will need to use the NOT EXISTS comparing the quantity using HAVING. The result is as follows:

SELECT base.nome as aquario,
       base.localizacao,
       base.nome_cientifico
  FROM (SELECT a.id_aquario,
               a.nome,
               a.localizacao,
               e.id_especie,
               e.nome_cientifico,
               count(1) as quantidade
          FROM aquario a
               INNER JOIN especime ep ON ep.id_aquario = a.id_aquario
               INNER JOIN especie e ON e.id_especie = ep.id_especie
         GROUP BY a.id_aquario, e.id_especie) base
  WHERE NOT EXISTS(SELECT 1
                     FROM especime ep
                          INNER JOIN especie e ON e.id_especie = ep.id_especie
                    WHERE ep.id_aquario = base.id_aquario
                      AND e.id_especie <> base.id_especie
                    GROUP BY e.id_especie
                   HAVING count(1) > base.quantidade)

Subqueries with EXISTS or NOT EXISTS

If the subquery return some line, EXISTS is TRUE, and NOT EXISTS is FALSE

HAVING CLAUSE

The clause HAVING was added to SQL because WHERE cannot be used with aggregate functions (GROUP BY)

A way to simplify your query would create a view with data crucial to the operation as follows:

CREATE VIEW view_quantidade_aquario AS
SELECT a.id_aquario,
       a.nome,
       a.localizacao,
       e.id_especie,
       e.nome_cientifico,
       count(1) as quantidade
  FROM aquario a
       INNER JOIN especime ep ON ep.id_aquario = a.id_aquario
       INNER JOIN especie e ON e.id_especie = ep.id_especie
 GROUP BY a.id_aquario, e.id_especie

And use it with the following query:

SELECT vw.nome as aquario,
       vw.localizacao,
       vw.nome_cientifico
  FROM view_quantidade_aquario vw
  WHERE NOT EXISTS(SELECT 1
                     FROM view_quantidade_aquario vw2
                    WHERE vw2.id_aquario = vw.id_aquario
                      AND vw2.id_especie <> vw.id_especie
                      AND vw2.quantidade > vw.quantidade)

In both cases the result will be:

 ______________________________________________________
| aquario             | localizacao | nome_cientifico  |
| _____________________________________________________|
| Vasco da Gama       | NO          | Lutra sumatrana  |
| Bartolomeu Dias     | N           | Lutra sumatrana  |
| Infante D. Henrique | S           | Torpedo torpedo  |
|______________________________________________________|

Browser other questions tagged mysql sql

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