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

Sort by

recency

|

2037 Discussions

|

  • mimperial1121
     + 0 comments
    SELECT w.id, 
       -- w.code,
       min_cost.age, 
       min_cost.mincost,
       w.power
FROM (
    SELECT w.code, wp.age, w.power, MIN(w.coins_needed) mincost
    FROM Wands w
    JOIN Wands_Property wp
        ON w.code = wp.code
    WHERE wp.is_evil = 0
        -- AND w.code = 2
    GROUP BY w.code, wp.age, w.power
    ) min_cost
-- JOIN Wands_Property wp
--     ON wp.code = min_cost.code
--     AND wp.age = min_cost.age
--     AND wp.is_evil = 0
JOIN Wands w
    ON w.code = min_cost.code
    AND w.power = min_cost.power
    AND w.coins_needed = min_cost.mincost
ORDER BY w.power DESC, min_cost.age DESC

  • phongngo4060
     + 1 comment

    I have a little bit problem with this one, I don't know why it have the wrong answer. Can someone can explain my issue. My code:

    SELECT id, age, coins_needed, power from wands inner join Wands_Property on wands.code = wands_property.code where is_evil = 0 order by power desc, age desc, coins_needed asc

  • siddharthdeshpa1
     + 0 comments

    /* display id, age, coins_needed, power where

    Hermione wants to see only the minimum price for every Power/Age combination in the results which is non evil

    so for each power & age combination find min_coins required and then sort

    For MySql -

    */

    select w.id,p.age,w.coins_needed,w.power from wands w join wands_property p on w.code=p.code where p.is_evil=0 and w.coins_needed = (select min(w1.coins_needed) from wands w1 join wands_property p1 on w1.code=p1.code where p.is_evil=0 and w.power=w1.power and p.code=p1.code) order by w.power desc,p.age desc

  • karankoushik7384
     + 0 comments

    For MySql -

    SELECT cta.id,cta.age,cta.coins_needed,cta.power FROM (SELECT a.id,a.coins_needed,a.power,b.age FROM Wands a LEFT JOIN Wands_Property b ON a.code = b.code WHERE b.is_evil = 0) cta WHERE (cta.age,cta.coins_needed,cta.power) IN (SELECT b.age,MIN(a.coins_needed),a.power FROM Wands a LEFT JOIN Wands_Property b ON a.code = b.code WHERE b.is_evil = 0 GROUP BY b.age,a.power) ORDER BY cta.power DESC,cta.age DESC

  • srikar13397
     + 0 comments
    select w.id,
p.age,
w.coins_needed,
w.power
from wands w
JOIN wands_property p
on w.code=p.code
where p.is_evil=0
and (p.age,w.coins_needed,w.power) in
(select max(p.age),min(w.coins_needed),max(w.power)
from wands w
 join wands_property p
 on w.code=p.code
 where p.is_evil=0
 group by w.power, p.age
)
order by w.power desc, p.age desc