We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
Loading...
  1. Practice
  2. SQL
  3. Basic Join
  4. Ollivander's Inventory
  5. Discussions

Ollivander's Inventory

  • shanu2608 + 3 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.

    • tingyuk + 0 comments

      Thank you for your explanation. This is really clear and helpful!

    • bonniegu0429 + 1 comment

      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)
      • brubru777 + 0 comments

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

    • caseyOnHack + 0 comments

      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