Background: Pokemon welcomed its 20th Anniversary in 2016. As the Pokemon Pikachu is the most iconic Pokemon, Moke club is planning to give out a one time award for the member that has caught the most number of Pikachu by sending an e-voucher through email.
Question: Find out the member who has caught the most number of Pikachu in the year 2016. Display the member name, member ID, number of Pikachu caught, and email address. Using subquery or subquery with inner join.
Relations involved: Participation, PokemomParticipation and Member (Below is a snapshot of the relation table. (Table has been modified to display result that is relevant to the query)
PokemomParticipation: PokemonParticipation
Pokemom: Pokemon
Member: Member
From the database, we can see that the member who has caught the most number of Pikachu has a memberID of 10 and its name is Marcus. The pokemon ID of Pikachu is 10. Hence, these three relations are needed to solve the questions.
My answer(Wrong Solution):
USE MokeClub
GO
SELECT m.MemberID, m.Name, m.Email,pp.NumCaught
FROM Member m INNER JOIN PokemonParticipation pp
ON m.MemberID = pp.MemberID
INNER JOIN Pokemon pk
ON pp.PokemonID =pk.PokemonID
WHERE pp.DateCaught >'2015-Dec-31' AND pp.DateCaught <'2017-Jan-01' AND pk.PokemonName ='Pikachu'
AND
NumCaught = (SELECT MAX(NumCaught)
FROM PokemonParticipation )
Picture: WRONG Solution The problem with this solution is that nothing is displayed.
Many thanks to your generous help! I have tried to research on the web, but unable to get out some meaning from the explanation. I am still a beginner to SQL query.
EDITED(CORRECT SOLUTIONS) Correct Solution
You are interested specifically for the row where NumCaught has a maximum value
for PokemonName ='Pikachu' and not for the maximum value of NumCaught in general.
So change the last condition to this:
AND
pp.NumCaught = (
SELECT MAX(t.NumCaught) FROM PokemonParticipation t
WHERE t.PokemonID = pk.PokemonID
)
See the demo