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

Question

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

Does this answer your question? [SQL query return data from multiple tables](https://stackoverflow.com/questions/12475850/sql-query-return-data-from-multiple-tables) — Filburt, Dec 07 '21 at 16:14

Joel Coehoorn · Answer 1 · 2021-12-07T16:15:19.067

0

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

edited Dec 07 '21 at 16:15

answered Dec 07 '21 at 16:11

Joel Coehoorn

399,467
113
570
794

Thanks for the response Joel. Care to explain all these m. p. w. letters ur using? – nostrad0muz Dec 07 '21 at 16:13
Those are aliases... mnemonics for the table names. It's good practice to use them when you have multiple tables in a query. The help save typing, avoid ambiguity for which column you're looking at, and make it possible to do things like reference separate instances of the same table in the same query. – Joel Coehoorn Dec 07 '21 at 16:16
I have not yet learned how to use so called mnemonics. Is there a more basic way to do it? And to answer your question about unclarity-- what we are after is counting the amount of times a County has had a citizen win the lottery, and to find out which county has had the most winning-incidents and how many times that county has had winners, and if that county has won more than once, there is to be a row for each win. – nostrad0muz Dec 07 '21 at 16:20
Looking to return rows only for that County who has won the most, but also amount of wins and have a seperate row per win – nostrad0muz Dec 07 '21 at 16:22
Then this is definitely a good time to learn to use aliases, because you'll want multiple instances of some tables. It's not exactly required, because you'll nest one set in it's own SELECT query, but it'll make it significantly easier to see what you're doing. – Joel Coehoorn Dec 07 '21 at 16:24

score 0 · Answer 2 · answered Dec 07 '21 at 17:43

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`

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

2 Answers2