How to get the value of one table depending on the maximum value of another?

Asked

Viewed 81 times

2

I have the following code that gives me a table like the picture, but my doubt is how do I only get the name and only the name of the one that has the highest health value that will be gasoline? It is possible to use a NOT EXISTS in this case?

SELECT O.nome, SUM(E.saude)
FROM Objecto O, Elemento E, composto C
WHERE O.marca = C.prodMarca AND C.produto = O.codigo AND C.elemento = E.codigo
GROUP BY O.nome

inserir a descrição da imagem aqui

mysql sql

  • But NOT EXISTS would be used with what other query?

2 answers

1


SELECT O.nome, SUM(E.saude) as valorSaude 
FROM Objecto O, Elemento E, composto C
WHERE O.marca = C.prodMarca AND C.produto = O.codigo AND C.elemento = E.codigo
GROUP BY O.nome order by valorSaude desc limit 1

So he orders by the valueSaude in descending form order by valorSaude desc and takes only the first result limit 1

  • And where is valorSaude in your select?

  • On the basis of the image he gave, I believe valorSaude be the SUM(E.saude) changed my answer

  • I just need to get the name and not the value. Is there another option using NOT EXISTS? (I forgot to refer to the question, I’ll change it now)

  • @Forcchape did not understand where you would like to use the NOT EXISTS in that case? Could you explain better?

  • I don’t know if it’s possible but creating a sub-question in WHERE with a NOT EXISTS ?

  • @Force how you get the valorSaude?

Show 1 more comment

1

Using a sub select would look that way, in my view:

SELECT O.nome, SUM(E.saude) as somaSaude
FROM Objecto O, Elemento E, composto C
WHERE O.marca = C.prodMarca AND C.produto = O.codigo AND C.elemento = E.codigo
AND somaSaude = (
    SELECT MAX(SUM(E.saude))
    FROM Objecto O, Elemento E, composto C
    WHERE O.marca = C.prodMarca AND C.produto = O.codigo AND C.elemento = E.codigo
)

  • Yes, but now that I realize it makes no sense to use a sub select for the case I’m making

Browser other questions tagged mysql sql

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