Why left join doesn't work here


  • 0
    A

    I came up a solution using left join, but it didn't pass the last two cases.
    I cannot see the test case completely, so I don't know why this solution is wrong.

    SELECT Name 
    FROM Candidate AS A
    LEFT JOIN Vote AS B
    ON A.id = B.CandidateId
    GROUP BY A.id
    ORDER BY Count(A.id) DESC
    LIMIT 1; 
    

  • 0
    C

    @a9887688zboy said in Why left join doesn't work here:
    I have the same question. I used right join:

    select c.name from candidate c
    right join vote v on c.id=v.candidateid
    group by c.name
    order by count(*) desc
    limit 1

    I came up a solution using left join, but it didn't pass the last two cases.
    I cannot see the test case completely, so I don't know why this solution is wrong.

    SELECT Name 
    FROM Candidate AS A
    LEFT JOIN Vote AS B
    ON A.id = B.CandidateId
    GROUP BY A.id
    ORDER BY Count(A.id) DESC
    LIMIT 1; 
    

  • 2
    L

    Because for base case: Vote table is empty table, this way will not work
    So, we must use sub-query to instead of Left Join
    I think this is the stupid situation in here
    Just ignore it


  • 2
    L

    They always have some very weird testing cases like this:

    THE MOST VOTED CANDIDATE IN VOTE TABLE MAY NOT BE IN THE CANDIDATE TABLE.

    Though it sounds strange, it is the case here:(

    Under such situation, the query should return an empty result.

    A reminder is that you have to find the most voted user in VOTE table first and then INNER JOIN the CANDIDATE table.


  • 0
    K

    Firstly, Candidate LEFT JOIN Vote is problematic. You will have greater number of votes than the number of candidates.

    Second, sub-query to determine the max voted Id in Vote table can omit the weird cases.


  • 0
    L

    @liu452 agree. In the test case, the output for empty vote table situation is 'A' and I thought this did not make sense.


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.