CODEWITHSUNDEEP
sqlsql-server
AT-2017
AT-2017

Reputation: 3149

Sql Returns Record If Even It Has No Record In Another Table

I've the following table structures:

Candidate:

CandidateId - CandidateName - Symbol
       2000 - Hillary - Mobile
       2001 - Trumph - Car
       2002 - Jackson - Laptop

CastVote:

        ID - VotedId - CandidateSymbol 
         1 - 234BB12344MK23468 - 2000
         2 - 234BB12344MK23469 - 2000
         2 - 234BB12344MK23470 - 2000
         2 - 234BB12344MK23471 - 2002

So the output would be as follows:

ID - Candidate - Votes - Status
 1 - 2000 - 3 - Winner
 2 - 2001 - 0 - Loser
 3 - 2002 - 1 - Loser

Again the output could be like this if any candidate has the same no. of votes:

 ID - Candidate - Votes - Status
 1 - 2000 - 2 - Winner
 2 - 2001 - 0 - Loser
 3 - 2002 - 2 - Winner

I've used the following query that works fine but if a candidate does not have any vote cast, it returns vote count for that candidate as 1:

 ID - Candidate - Votes - Status
  1 - 2000 - 2 - Winner 
  2 - 2001 - 1 - Loser
  3 - 2002 - 2 - Winner //Returns 1 even it has no record in the **CastVote** table

Used the following query:

 WITH MyCTE AS (
                SELECT k.CandidateName, k.Symbol, COUNT(*) AS Votes 
                  FROM CastVote m 
       FULL OUTER JOIN Candidate k 
                    ON k.CandidateId = m.CandidateSymbol 
              GROUP BY CandidateSymbol, k.CandidateName, k.Symbol
               )
     SELECT Symbol, CandidateName, Votes,CASE 
       WHEN DENSE_RANK() 
       OVER (ORDER BY Votes DESC) = 1
       THEN 'Winner' ELSE 'Loser' END Status 
       FROM MyCTE

Upvotes: 1

Views: 62

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522499

Common table expressions really save the day here, allowing us to do away with what would have been a really ugly subquery to determine the status for each candidate. Many times not being able to handle a tie is a problem, but in your case you are OK with having more than one winner, in which case we don't have to worry about there being more than one candidate with the greatest number of votes.

WITH Votes_CTE (CandidateSymbol, Votes)
AS
(
    SELECT CandidateSymbol, COUNT(*) AS Votes
    FROM CastVote
    GROUP BY CandidateSymbol
)

SELECT t1.ID,
       t1.CandidateId AS Candidate,
       t2.Votes,
       CASE WHEN t2.Votes = (SELECT MAX(Votes) FROM Votes_CTE)
            THEN 'Winner'
            ELSE 'Loser' END AS Status
FROM Candidate t1
LEFT JOIN Votes_CTE t2
    ON t1.CandidateId = t2.CandidateSymbol

Upvotes: 1

Related Questions