I have 3 tables each containing election_id election_year election_type (which is a string), party_name(also a string) party_ID, and the last one has Results_ID(equal to election_id) Results_party(equal to party_ID) and lastly ballots, my query has to return max ammount of ballots each party has ever gotten for the years in consideration for example(also desired output):

I tried this

SELECT Party.party_name,Elections.election_year,Results.Results_ballots
FROM ((Elections 
INNER JOIN Results 
   on Elections.election_ID=Results.Results_Elections 
   AND Elections.election_Type='Regional') 
INNER JOIN Party on Party.party_ID=Results.Results_Party)

but this gives me all the parties and all the elections with all the ballots, I want max ballot count for each one any ideas are appreciated, I'm very new to MySQL

EDIT: also tried this, which missed the last row of the table

FROM Party as p, Elections as e, Results as r
where p.party_ID=r.Results_Party and e.election_Type='Regional'  AND r.Results_Elections=e.Elections_ID
group by p.party_name

So it looked like this

CodePudding user response：

If I understood you correctly, you have to do a subquery to get the max for each party.

this is what I came up with

SELECT * FROM Party c
LEFT JOIN Results b
ON b.Results_party=c.party_ID
AND b.ballots=(SELECT MAX(d.ballots) FROM Results d WHERE d.Results_Party=c.party_ID)
LEFT JOIN Elections a
ON a.Elections_ID=b.Results_ID