# 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.

Recommended SQL Courses:

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:

• Wands: The id is the id of the wand, code is the code of the wand, coins_needed is the total number of gold galleons needed to buy the wand, and power denotes the quality of the wand (the higher the power, the better the wand is).

• Wands_Property: The code is the code of the wand, age is the age of the wand, and is_evil denotes whether the wand is good for the dark arts. If the value of is_evil is 0, it means that the wand is not evil. The mapping between code and age is one-one, meaning that if there are two pairs, (code1, age1) and (code2, age2), then code1!=cod2 and age1!=age2.

Sample Input:

Wands Table:

Wands_Property Table:

Sample Output:

`9 45 1647 1012 17 9897 101 20 3688 815 40 6018 719 20 7651 611 40 7587 510 20 504 518 40 3312 320 17 5689 35 45 6020 214 40 5408 1`

Explanation:

The data for wands of age 45 (code 1):
• 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 data for wands of age 40 (code 2):
• 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 data for wands of age 20 (code 4):

• 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 data for wands of age 17 (code 5):
• 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.POWERFROM  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.CODEWHERE WP.IS_EVIL = 0ORDER 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 101130 494 9439 101315 492 4126 109 491 7345 10858 483 4352 101164 481 9831 101288 464 4952 10861 462 8302 10412 455 5625 10996 451 8884 101608 446 8351 101376 443 1735 101330 430 5182 10`

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