Pass a subquery from the FROM clause to the WHERE clause [duplicate]

Question

Pass a subquery from the FROM clause to the WHERE clause [duplicate]

Navigation

#1 by (1 votes)

-3

I have the following query with a subquery in the FROM clause and I want to get the same result but using this subquery in the WHERE clause.

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 Espe ON Espe.id_aquario = A.id_aquario 
                         INNER JOIN Especie E ON E.id_especie = Espe.id_especie
         GROUP BY A.id_aquario, E.id_especie) base
  WHERE NOT EXISTS(SELECT 1
                   FROM Especime Espe2, Especie E2
                   WHERE Espe2.id_aquario = base.id_aquario AND E2.id_especie <> base.id_especie AND E2.id_especie = Espe2.id_especie
                   GROUP BY E2.id_especie
                   HAVING count(1) > base.quantidade);

The purpose of this query is to indicate the scientific names, location, and name of the aquarium, the most numerous species in this aquarium.

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


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

-- D (Num5, Num2, perce)
insert into D values(1,1,100);
insert into D  values(2,1,100);
insert into D values(1,3,10);
insert into D values(3,3,20);
insert into D values(2,2,60);
insert into D values(3,1,50);
insert into D values(2,3,70);
insert into D values(3,2,40);
insert into D values(2,1,0);

mysql sql

asked by anonymous 07.12.2016 / 23:22

1 answer

Charge of delivery fee: sometimes it works and sometimes not [closed] Return Java Trigger [closed]

score 1 · Accepted Answer

Although not the best way, following what was required in the question the result would be something like this. It is not recommended to use the same query so many times in this way and it is far from being a good practice:

SELECT a.nome AS aquario,
       a.localizacao,
       e.nome_cientifico
  FROM aquario a
       INNER JOIN especime em ON em.id_aquario = a.id_aquario
       INNER JOIN especie e ON e.id_especie = em.id_especie
 WHERE (SELECT COUNT(1) as quantidade
          FROM especime em2
         WHERE em2.id_aquario = a.id_aquario
           AND em2.id_especie = e.id_especie
         GROUP BY em2.id_aquario, em2.id_especie) >
IFNULL((SELECT COUNT(1) as quantidade
          FROM especime em2
         WHERE em2.id_aquario = a.id_aquario
           AND em2.id_especie <> e.id_especie
         GROUP BY em2.id_aquario, em2.id_especie), 0)
GROUP BY a.nome,
         a.localizacao,
         e.nome_cientifico