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.
  1. Prepare
  2. SQL
  3. Basic Join
  4. Ollivander's Inventory
  5. Discussions

Ollivander's Inventory

  • tsgilbert
     + 0 comments

    mySQL - this one was confusing to me but doing it like this seemed to make the most sense to me:

    SELECT w.id, wp.age, w.coins_needed, w.power
FROM Wands w
JOIN Wands_Property wp ON w.code = wp.code
JOIN (
    SELECT wp.age, w.power, MIN(w.coins_needed) AS min_cost
    FROM Wands w
    JOIN Wands_Property wp ON w.code = wp.code
    WHERE wp.is_evil = 0
    GROUP BY wp.age, w.power
) AS best ON wp.age = best.age 
         AND w.power = best.power 
         AND w.coins_needed = best.min_cost
WHERE wp.is_evil = 0
ORDER BY w.power DESC, wp.age DESC;