Pass a subquery of the FROM clause to the WHERE clause

Asked

Viewed 357 times

-3

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

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, of the species in the largest number in this aquarium.

Tables required:

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

  • Dude, you gotta tell me what the point of query right. If you keep to yourself the information you think someone will be struggling to help you?

  • I would have, but it’s not showing up :/ And I tried to edit it but it’s not showing up either

  • Is already corrected

  • 1

    The statement "The purpose of this query is to indicate the scientific names, location and name of the aquarium, of the species in the largest number in this aquarium." is the same as http://answall.com/questions/169108/70 which was already duplicated?

  • What I want is to change the code above to the WHERE clause , I do not think it is equal , the answer that it is not what I want

  • 1

    It would be good to explain the difference then, because if the answer fits in that, it is duplicated in any way. The fact that the answer does not meet does not necessarily justify another posting. It would be very clear to highlight the different requirements (actual requirements), so avoid qq misunderstanding or closure.

  • Yes, it is written in the question and the title , the requirements is that there is no subquery’s in the FROM clause but in the WHERE clause , it is different from the other question

  • 1

    And another, in the INNER JOIN can subquery? It’s not very clear

  • 3

    Well, it seems to me that the problem lies in the formulation of the first question already. However, it is suggested that in the next questions we should detail better and more clearly to avoid a series of similar posts, which can even help you obtain more complete solutions from the beginning, and at the same time take better advantage of the time of those who answer.

  • @Sorack The only requirement to change the above code is only if the FROM clause has no subquery.

  • @Bacco Ok I’m beginning to understand better how to ask the questions

  • You don’t want to view this case?

  • @Sorack view despite being better for this case , the ideal was to just use the " ideas " that are in the code above

Show 8 more comments

1 answer

1


Although it is not the best way, following what was required in the question the result would be more or less this. It is not recommended to use the same query so many times in this way and it is far from 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

  • I think I’m missing something that I’m missing , the subquery’s in this case are not within the FROM clause ?

  • I answered only that the requirement is that there is no subquery in the FROM clause , that’s all that matters in changing the code I give , it’s just that the only obstacle to the code I have to be correct, I didn’t realize at the time q vc was talking about JOIN

  • Yes I just answered that of the requirement. This question is the most difficult and last of a lot of 25 questions I have to ask and I’m not even realizing more or less how I have to do this considering that it cannot be in the FROM clause , in principle the subquery’s should be in the WHERE clause

  • This question was one of the few that I could not change, because in the others there were many that I had also done with subquery’s in FROM and had to change and fortunately I was able to do well most of them , this had already tried but I did not get any concrete answer.

  • @Forcchape although you have changed your mind as to what you had put in the question comment, there is the answer.

Browser other questions tagged mysql sql

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