# HackerRank: [SQL Basic Join] (6/8) OLLIVANDER'S INVENTORY | inner join, min & Sub-Query in SQL

I started studying SQL from a very famous site - HackerRank. Here I will try to provide multiple approaches & solutions to the same problem. It will help you learn and understand SQL in a better way.

Please make use of my blog posts for learning purpose only and feel free to ask your questions in the comment box below in case of any doubt.

SQL Problem Statement:

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.

Input Format:

The following tables contain data on the wands in Ollivander's inventory:

Sample Input:

Wands Table:

Wands_Property Table:

Sample Output:

```9 45 1647 10
12 17 9897 10
1 20 3688 8
15 40 6018 7
19 20 7651 6
11 40 7587 5
10 20 504 5
18 40 3312 3
20 17 5689 3
5 45 6020 2
14 40 5408 1```

Explanation:

• The minimum number of galleons needed for wand(age=45, power=2) = 6020
• The minimum number of galleons needed for wand(age=45, power=10) = 1647
• The minimum number of galleons needed for wand(age=40, power=1) = 5408
• The minimum number of galleons needed for wand(age=40, power=3) = 3312
• The minimum number of galleons needed for wand(age=40, power=5) = 7587
• The minimum number of galleons needed for wand(age=40, power=7) = 6018
• The minimum number of galleons needed for wand(age=20, power=5) = 504
• The minimum number of galleons needed for wand(age=20, power=6) = 7651
• The minimum number of galleons needed for wand(age=20, power=8) = 3688
• The minimum number of galleons needed for wand(age=17, power=3) = 5689
• The minimum number of galleons needed for wand(age=17, power=10) = 9897

### Solution: Using INNER JOIN & HAVING (MySQL Query):

```SELECT
W.ID,
WP.AGE,
M.MIN_COINS_NEEDED,
W.POWER
FROM
WANDS W
JOIN (SELECT CODE, POWER, MIN (COINS_NEEDED) as MIN_COINS_NEEDED FROM WANDS GROUP BY CODE, POWER) M
ON W.CODE = M.CODE AND W.POWER = M.POWER AND W.COINS_NEEDED = M.MIN_COINS_NEEDED
JOIN WANDS_PROPERTY WP ON M.CODE = WP.CODE
WHERE WP.IS_EVIL = 0
ORDER BY M.POWER DESC, WP.AGE DESC;```

NOTE:
1. MIN is an aggregation function used to get the least (minimum) value of all the records in the column name passed to the function.

2. JOIN and INNER JOIN are the same in SQL. It returns the records that have matching values in both tables.

### Expected Output:

```1038 496 4789 10
1130 494 9439 10
1315 492 4126 10
9 491 7345 10
858 483 4352 10
1164 481 9831 10
1288 464 4952 10
861 462 8302 10
412 455 5625 10
996 451 8884 10
1608 446 8351 10
1376 443 1735 10
1330 430 5182 10```

--------------------------------------------------------------------------------