Get elements of a dependent column from another [duplicate]

Question

Get elements of a dependent column from another [duplicate]

Navigation

#1 by (2 votes)

1

I have to make a Table that indicates 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

asked by anonymous 01.12.2016 / 20:12

1 answer

Leave input wrapped with strokes when using Jquery Validate How to create pattern with jQuery Validation Plugin?

score 2 · Accepted Answer

You can use subquery to get the amount of each species per aquarium. But to show only one record per aquarium you will have to use NOT EXISTS by 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 subquery returns some line, EXISTS is TRUE , and NOT EXISTS is FALSE

HAVING Clause

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

One way to simplify your query would be to create a view with the 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  |
|______________________________________________________|