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

  • wjldrew + 3 comments

    MySQL. In the beginning I had trouble with the output because SELECT output wands with a minimum coins needed that didn't always match with that wand.

    There is a sub-query to make sure that if the wand age and wand power matches with another, to return the cheapest (lowest coins_needed) wand with.

    SELECT W.id, P.age, W.coins_needed, W.power
FROM WANDS AS W
    INNER JOIN WANDS_PROPERTY AS P ON W.code = P.code
WHERE P.is_evil = 0 AND W.coins_needed = 
    (SELECT MIN(coins_needed)
     FROM WANDS AS W1
        INNER JOIN WANDS_PROPERTY AS P1 ON W1.code = P1.code
     WHERE W1.power = W.power AND P1.age = P.age)
ORDER BY W.power DESC, P.age DESC
    • achalshah20 + 1 comment

      Can you please explain how 

      W.coins_needed = 
(SELECT MIN(coins_needed)
FROM WANDS AS W1
INNER JOIN WANDS_PROPERTY AS P1 ON W1.code = P1.code
WHERE W1.power = W.power AND P1.age = P.age)

      is different than

      group by W.power, W.age
having MIN(W.coins_needed)

      Thanks

      • payalbhatia_ece + 0 comments

        Please follow the link and interpret the output by running query separately :

        https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns

    • yingli40604345 + 0 comments

      May I know what does this part mean? Why use W1 with W and P1 with P?Thank you so much!!! SELECT MIN(coins_needed) FROM WANDS AS W1 INNER JOIN WANDS_PROPERTY AS P1 ON W1.code = P1.code WHERE W1.power = W.power AND P1.age = P.age)

    • christianwarnold + 0 comments

      select s.id, s.age, s.coins_needed, s.power from (select w.id, wp.age, w.coins_needed, w.power from Wands as w inner join Wands_Property as wp on w.code = wp.code) as s inner join (select wp.age, min(w.coins_needed) as coins_needed, w.power from Wands as w inner join Wands_Property as wp on w.code = wp.code where wp.is_evil = 0
      group by wp.age, w.power order by w.power desc, wp.age desc) as t on s.age = t.age and s.coins_needed = t.coins_needed and s.power = t.power order by t.power desc, t.age desc