Trying to understand Joins

user_12968250 0
Viewed 310

I am trying to practice sql by myself and I was solving the questions from the website hackerrank. I came across to this question called " Ollivander's Inventory " ( here's a link if you want to see the question in detail other than my explanation https://www.hackerrank.com/challenges/harry-potter-and-wands/problem)

Question goes as

"Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand.

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age."

I found the solution that was pretty close to my code but still there is a part that i didnt understand in this solution. Code is:

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
Where wp.is_evil!=1 AND w.coins_needed=(Select min(Wands.coins_needed) 
from Wands
inner join Wands_Property 
on Wands.code=Wands_Property.code 
where Wands_Property.age=wp.age and Wands.power=w.power)
order by   w.power desc, wp.age desc;

I didnt understand this part of the code.

AND w.coins_needed=(Select min(Wands.coins_needed) 
from Wands
inner join Wands_Property 
on Wands.code=Wands_Property.code 
where Wands_Property.age=wp.age and Wands.power=w.power)

As I know (correct me if im wrong please) we coudlnt use min(wands.coins_needed) in the beginning of the query because after wards we would need to use group by clause and it would cause problem on w.id. But what is the reason why we used another join after the w.coins_needed=(Select min(Wands.coins_needed)? Any help would be appricated. Please show me where im thinking wrong.

Thanks!

sql-server mysql sql
3 Answers

OK, you likely understand this, but just to be clear and answer completely, the query, (based on Hermione's suggestion) is listing every combination of power and age for all the wands listed, but when more than one wand has the same power and age, only list the least expensive one.

The outer query is going through all the wands.

The inner query, will query for all the other wands with the same power and age to find the cheapest price.

To do that, the inner query needs to take the power and age of the "current" wand from the outer query.

To query for all wands with the same power and age, we need to join the Wands and Wands_Property tables. This is the answer. We need both tables to match power and age in our inner query looking for the cheapest price of all equivalent wands.

Once we know the cheapest price we can get a wand with a given power and age, we can have a condition in our WHERE clause to only show those wands.

user_8315640

I'm not sure what your example is trying to do -- it looks like it has a bug to me. Here is how I would do it -- because I think it makes it clear:

SELECT w.id, wp.age, w.coins_needed, w.power
FROM wands as w
JOIN wands_property as wp on w.code=wp.code
JOIN (
  SELECT wp.age, w.power, min(w.coins_needed) as c
  FROM wands as w
  JOIN wands_property as wp on w.code=wp.code
  WHERE wp.is_evil != 1
  GROUP BY wp.age, w.power
) mi on wp.age = mi.age and w.power = mi.power and w.coins_needed = mi.c
WHERE wp.is_evil != 1

I use the same base join to get all combinations, but in the joined table I do a group by and get the least cost. Then I just join back to the main query to get the result.

You could also use a windowing function -- that would like this:

SELECT id, age, coins_needed, power
FROM (
  SELECT w.id, wp.age, w.coins_needed, w.power
       ROW_NUMBER(PARTITION BY wp.age, w.power ORDER BY w.coins_needed ASC) AS RN
  FROM wands as w
  JOIN wands_property as wp on w.code=wp.code
  WHERE wp.is_evil != 1
) X
WHERE RN = 1
user_2157520

FOR MYSQL

SELECT w.id, wp.age, w.coins_needed, w.power
FROM wands w INNER JOIN wands_property wp ON w.code = wp.code
WHERE wp.is_evil = 0 AND w.coins_needed = (SELECT MIN(w2.coins_needed)
                                          FROM wands w2 INNER JOIN wands_property wp2 ON w2.code = wp2.code
                                          WHERE w2.code = w.code AND wp2.is_evil = 0 AND w2.power = w.power)
ORDER BY w.power DESC, wp.age DESC;
user_126149830
Related