## 574. Winning Candidate

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 winner `B`.

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

Notes:

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

b'
\n\n

## Solution

\n
\n

#### Approach: Using `JOIN` and a temporary table [Accepted]

\n

Algorithm

\n

Query in the Vote table to get the winner\'s id and then join it with the Candidate table to get the name.

\n

MySQL

\n
`SELECT\n    name AS \'Name\'\nFROM\n    Candidate\n        JOIN\n    (SELECT\n        Candidateid\n    FROM\n        Vote\n    GROUP BY Candidateid\n    ORDER BY COUNT(*) DESC\n    LIMIT 1) AS winner\nWHERE\n    Candidate.id = winner.Candidateid\n;\n`
\n
'