You are viewing a single comment's thread. Return to all comments →
If you use group by , the select( min(coins_needed) ....query will return multiple rows and not a single value.
So, we can't write a condition such as : where coins_needed=select(min(coins_needed) ... group by...) because of multiple rows problem.
Thank you for your explanation. This is really clear and helpful!
Why I cannot use the subquery like this
coins_needed = any(select min(coins_needed) from Wands as w1 join Wands_Property as p1 on (w1.code = p1.code) group by age, power)
"any" returns true or false. You can't use it to compare it to "coins_needed".
Here, you just need to remove the "any" and add a where clause to match the correct age and power.
what about adding "HAVING " statement:
HAVING w1.power = w.power and p1.age = p.age
after GROUP BY
Cause this also worked:
SELECT w.id, wp.age, w.coins_needed, w.power
FROM Wands AS w JOIN Wands_Property AS wp on (w.code = wp.code)
WHERE wp.is_evil=0 AND w.coins_needed = (SELECT min(coins_needed)
FROM Wands AS w1 JOIN Wands_Property AS wp1 ON (w1.code = wp1.code)
GROUP BY w1.power, wp1.age
HAVING w1.power = w.power AND wp1.age = wp.age)
ORDER BY w.power DESC, wp.age DESC