I have the following code that gives me a table like the one in the picture, but my question is how do I get the name and just the name of the one that has the highest health value that will be the gas? Is it 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
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 sorts by valueSaude in descending order order by valorSaude desc and takes only the first result limit 1
Using sub select would look like this, as I see it:
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
)