Table:Candidate

+-----+---------+
| id  | Name    |
+-----+---------+
| 1   | A       |
| 2   | B       |
| 3   | C       |
| 4   | D       |
| 5   | E       |
+-----+---------+

Table:Vote

+-----+--------------+
| id  | CandidateId  |
+-----+--------------+
| 1   |     2        |
| 2   |     4        |
| 3   |     3        |
| 4   |     2        |
| 5   |     5        |
+-----+--------------+
id is the auto-increment primary key,
CandidateId is the id appeared in Candidate table.

Write a sql to find the name of the winning candidate, the above example will return the winnerB.

+------+
| Name |
+------+
| B    |
+------+

Notes:

1. You may assume there is no tie , in other words there will be at most one winning candidate.

Solution

Approach: UsingJOINand a temporary table [Accepted]

Algorithm

Query in theVotetable to get the winner's id and then join it with theCandidatetable to get the name.

MySQL

select name from(

select Name, count(Name) as cnt

from Vote left join Candidate on Vote.CandidateId = Candidate.Id

Group by Name

ORDER BY cnt)

limit 1;