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.
- Prepare
- SQL
- Basic Join
- Ollivander's Inventory
- Discussions
Ollivander's Inventory
Ollivander's Inventory
Sort by
recency
|
1773 Discussions
|
Please Login in order to post a comment
SELECT w.id, wp.age, filt.cost, w.power FROM WANDS w JOIN WANDS_PROPERTY wp on w.code = wp.code JOIN ( SELECT filt_wp.age, MIN(filt_w.coins_needed) AS cost, filt_w.power FROM WANDS filt_w JOIN WANDS_PROPERTY filt_wp on filt_w.code = filt_wp.code WHERE filt_wp.is_evil = 0 GROUP BY filt_wp.age, filt_w.power ) AS filt ON filt.age = wp.age AND filt.power = w.power AND filt.cost = w.coins_needed ORDER BY w.power DESC, wp.age DESC;
MySQL
--First Selcet the needed data -- SELECT W.id, WP.age, W.coins_needed, W.power FROM Wands W JOIN Wands_Property WP ON W.code = WP.code -- Apply the needed Selection Condition -- -- we need the minium coins while having the same power and age -- -- we also need to exculde the evil wands -- WHERE W.coins_needed = ( SELECT MIN(W1.coins_needed) FROM Wands W1 JOIN Wands_Property WP1 ON W1.code = WP1.code WHERE WP1.is_evil = 0 AND W1.power = W.power AND WP1.age = WP.age ) ORDER BY W.power DESC, WP.age DESC;
OPTIMISED
with cte as (select w.id, wp.age, w.coins_needed, w.power from wands w join wands_property wp on w.code = wp.code where wp.is_evil = 0 )
select id, age, coins_needed, power from cte c1 where coins_needed = ( select min(coins_needed) from cte c2 where c1.age = c2.age and c1.power = c2.power) order by power desc, age desc ;
select w.id, wp.age, w.coins_needed, w.power from wands w join wands_property wp on w.code=wp.code where coins_needed = (select min(w1.coins_needed) from wands w1 join wands_property wp1 on w1.code=wp1.code where is_evil =0 and w1.power=w.power and wp1.age = wp.age) order by power desc, age desc;