MySQL Interview ,which made me disqualified for the job [closed]

Question

I was given this MySQL Interview Question, which made me disqualified for the job.

I went to interview and was asked a question which I was not able to reply and I lost the job.

They asked.

We have two tables, the first table (master table) is CANDIDATES with fields:

• candidate_id (primary_key)
• candidate_name

The Second table (child table) is CANDIDATE_VOTES with fields:

• v_id (primary key)
• candidate_id (foreign key)

Every time a vote is given the candidate's candidate_key is put in the child.

CANDIDATE:

=================================     
|candidate_id | candidate_Name  |  
|-------------------------------|  
| 1           | abc             |  
|-------------------------------|  
| 2           | xyz             |  
|-------------------------------|  
| 3           | etc             |  
|-------------------------------|  

CANDIDATE VOTES

==========================     
| votes_id | candidate_id |  
|-------------------------|  
| 1        | 1            |  
|-------------------------|  
| 2        | 1            |  
|-------------------------|  
| 3        | 2            |  
|-------------------------| 

The Question was how would you declare a winner?

Please help me how to do it.

I tried a lot but could not find the logic.

Answer 1

You should return all candidates who have the most number of votes:

SELECT   candidates.*
FROM     candidates JOIN candidate_votes USING (candidate_id)
GROUP BY candidate_id
HAVING   COUNT(*) = (
  SELECT   COUNT(*)
  FROM     candidate_votes
  GROUP BY candidate_id
  ORDER BY votes DESC
  LIMIT    1
)

See it on sqlfiddle.

Answer 2

You can use a COUNT() to get the total number of votes associated with each candidate. By joining the two tables, you can return the candidate_name and if you use a LIMIT and ORDER BY on the query it will return only the record determined as the winner.

SELECT count(*) winner, c.candidate_Name
FROM candidates c
INNER JOIN candidate_votes cv
   ON c.candidate_id = cv.candidate_id
GROUP BY c.candidate_Name
ORDER BY winner desc
LIMIT 1  -- remove the LIMIT to see all records

See SQL Fiddle with Demo

or MySQL allows to GROUP BY on fields not in the SELECT

SELECT count(*) winner, , c.candidate_Name
FROM candidates c
INNER JOIN candidate_votes cv
   ON c.candidate_id = cv.candidate_id
GROUP BY cv.candidate_id
ORDER BY winner desc
LIMIT 1 -- remove the LIMIT to see all records

See SQL Fiddle with Demo