How do add data to multiple columns at the same time-CodePudding

I got this last task before I can go to bed...

Make a query that shows the name(not the id) of players who have won the lottery more than once, how many times they've won and the name(not the id) of the municipality they live in.

Players-table: PlayerNum, Name, Address, MunicipalityID

Winners-table: PlayerNum, DrawID

Municipality-table: MunicipalityID, County, Population, Name

Thank you sooo much in advance!!

CodePudding user response：

You need to join the tables and do a sub query on the winner table using count and group by the join the result set with player

Not sure what the draw table does

CodePudding user response：

You really should make an attempt instead of just asking for the solution.

Your starting point is to find the users who have won more than once. This is a simple GROUP BY of PlayerNum and the HAVING clause to limit the result based on the COUNT -

SELECT PlayerNum, COUNT(DrawID) AS num_wins
FROM Winners
GROUP BY PlayerNum
HAVING num_wins > 1

The next step is to add the names of the players. For this you need to join to the Players table and I have added table aliases (w & p) to avoid retyping the full table name each time -

SELECT p.Name, COUNT(DrawID) AS num_wins
FROM Winners w
INNER JOIN Players p
    ON w.PlayerNum = p.PlayerNum
GROUP BY w.PlayerNum
HAVING num_wins > 1

And then finally the join to Municipality to get the Name with a column alias as we already have a Name column -

SELECT p.Name, COUNT(DrawID) AS num_wins, m.Name AS MunName
FROM Winners w
INNER JOIN Players p
    ON w.PlayerNum = p.PlayerNum
INNER JOIN Municipality m
    ON p.MunicipalityID = m.MunicipalityID
GROUP BY w.PlayerNum
HAVING num_wins > 1