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

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

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