How can I make a query that collects information from multiple tables?-CodePudding

Ok this is going to be an extensive one. All help is very appreciated.

I'm working on a task where I have schema for a Lottery.

The task is to make a query that returns which County (not country) has had the most winners in total, a count for how many winners that County has had, if a County has won more than once they are to be counted for every time they've won.

In the Municipality Table we have MunicipalityID, County, Population and Name --

In the Player Table we have PlayerNum, Name, Address and MunicipalityID --

In the County Table we have CountyID, Population and Name --

In the Winner Table we have PlayerNum and DrawID --

CodePudding user response：

There is one point in the question that is not entirely clear.

On the one had, we are counting "Winners" (not wins). That suggests would should be a COUNT(DISTINCT PlayerNum) within each county.

On the other hand, we see "if a county has won more than once they are to be counted for every win.", which suggests a simple COUNT(*) within the county groups.

SELECT m.County, COUNT(*) As Wins
FROM Winner w
INNER JOIN Player p on p.PlayerNum = w.PlayerNum
INNER JOIN Municipality m on m.MunicipalityID = p.MunicipalityID
GROUP BY m.County
ORDER BY COUNT(*) DESC
LIMIT 1

CodePudding user response：

You will find you get more help on here if you put more effort into your questions, including CREATE TABLE statements, sample data and an example of the output you are looking for.

Further to Joel's answer, you can use the result to then retrieve the detail of the wins -

SELECT
    `most_wins`.`County`,
    `most_wins`.`Wins`,
    `m`.`Name` AS `MunicipalityName`,
    `p`.`Name` AS `PlayerName`,
    `w`.`DrawID`
FROM (
    SELECT m.County, COUNT(*) As Wins
    FROM Winner w
    INNER JOIN Player p on p.PlayerNum = w.PlayerNum
    INNER JOIN Municipality m on m.MunicipalityID = p.MunicipalityID
    GROUP BY m.County
    ORDER BY COUNT(*) DESC
    LIMIT 1
) AS `most_wins`
INNER JOIN `Municipality` `m` ON `m`.`County` = `most_wins`.`County`
INNER JOIN `Player` `p` ON `p`.`MunicipalityID` = `m`.`MunicipalityID`
INNER JOIN `Winner` `w` ON `w`.`PlayerNum` = `p`.`PlayerNum`