# JUNTO Practice: Ollivander's Inventory

Discussed on October 06, 2019.

Problem is "Ollivander's Inventory".

Hint: This query is looking for the cheapest wand for a given combination of age and power. In the `WHERE` clause of your query, use a subquery to make sure the it has the minimum coins needed compared to all other wands of the same age and power.

## Solutions

Click to see:

### Oscar Martinez

``````SELECT
good.id,
good.age,
cheap.coins_needed,
good.power
FROM (
SELECT
WP.age,
MIN(W.coins_needed) as coins_needed,
W.power
FROM Wands as W
LEFT JOIN Wands_Property as WP
ON W.Code=WP.Code
WHERE WP.is_evil = 0
GROUP BY W.power,WP.age
) as cheap
NATURAL JOIN (
SELECT
W2.id,
W2.coins_needed,
W2.power,
WP2.age
FROM Wands as W2
LEFT JOIN Wands_Property as WP2
ON W2.Code = WP2.Code
WHERE WP2.is_evil = 0
) as good
ORDER BY good.power DESC,good.age DESC;
``````

Looks like this and John's solution have a similar technique.

-- Daniel

In this case I don't think you need to use a left join, and in my opinion an inner join would be more clear. It looks like you are using a similar structure to my solution, your subqueries are named `cheap` and `good` which correspond with my `Good_Wands` and `Cheapest`.

-- John

### John Lekberg

``````WITH
Good_Wands(id, coins_needed, power, age) AS (
SELECT id, coins_needed, power, age
FROM Wands
INNER JOIN Wands_Property
ON Wands.code = Wands_Property.code
WHERE is_evil = 0
),
Cheapest(coins_needed, power, age) AS (
SELECT MIN(coins_needed), power, age
FROM Good_Wands
GROUP BY power, age
)
SELECT id, age, coins_needed, power
FROM Cheapest NATURAL JOIN Good_Wands
ORDER BY power DESC, age DESC
;
``````

I'm using a natural join instead of an inner join because I designed the common table expressions `Good_Wands` and `Cheapest` to have corresponding attributes. An equivalent inner join would look like

``````...
FROM Good_Wands
INNER JOIN Cheapest
ON Good_Wands.coins_needed = Cheapest.coins_needed
AND Good_Wands.power = Cheapest.power
AND Good_Wands.age = Cheapest.age
...
``````

Daniel, I thought about naming tables after our discussion last week.

-- John

I like the use of the CTE.

-- Daniel

### Daniel Bassett

``````SELECT W.ID, P.AGE, W.COINS_NEEDED, W.POWER
FROM WANDS W, WANDS_PROPERTY P
WHERE W.CODE = P.CODE AND P.IS_EVIL = '0' AND COINS_NEEDED = (SELECT MIN(COINS_NEEDED) FROM WANDS WHERE CODE = W.CODE AND POWER =W.POWER)
GROUP BY P.AGE, W.COINS_NEEDED, W.ID, W.POWER
ORDER BY W.POWER DESC, P.AGE DESC;
``````

Instead of using a cross join and a condition in the where clause I think you should just do an inner join. Changing

``````...
FROM WANDS W, WANDS_PROPERTY P
WHERE W.CODE = P.CODE AND ...
``````

to

``````...
FROM WANDS W
INNER JOIN WANDS_PROPERTY P
ON W.CODE = P.CODE
WHERE ...
``````

I don't think the group by clause is needed in this case. (Test it out just to confirm that.) I also really like your use of the correlated subquery

``````COINS_NEEDED = (SELECT MIN(COINS_NEEDED) ...)
``````

I find it easy to read, and I think it's a good design choice.

-- John