CODEWITHSUNDEEP
mysqlif-statementcase
Pascal Klein
Pascal Klein

Reputation: 24863

MySQL: check if data exist inside query

I never used "if-else" or "case" inside a sql-query, but I guess I need to this time. I have a table whose data represents something like a competition between two users:

//properties of "competition-table
    int competitionId
    int userId_Contrahent1
    int userId_Contrahent2
    otherdata....

Users can vote for one or the other contrahent within that competition; a vote is represented like so:

//properties of vote-table
    int voteId
    int competitionId
    int userId_Voter
    int userId_Winner // the user for which this vote counts
    otherdata....

Obviously every given user in my Webapplication can only vote once for any given competition. So when I query for the competition I also want to have the information if the currentUser (which owns the current session) already voted for this competition. So in addition to all the other properties of competition I like to have an additional one, that has a key like "voted" and a value like "yes" or "no". So my select statement should look something like this I guess:

SELECT competition.*,
If EXISTS ( 
  SELECT * FROM vote WHERE userId_Voter = $currentUserId 
  AND competitionId = competition.competitionId)
  ...do something
FROM competition;

How do I do that exactly in MySQL?

Upvotes: 1

Views: 1564

Answers (1)

zerkms
zerkms

Reputation: 254916

   SELECT c.*,
          IF(v.competitionId IS NOT NULL, 'voted', 'not voted') AS `verdict`
     FROM competition c
LEFT JOIN vote v ON v.competitionId = c.competitionId
                AND v.userId_Voter = $currentUserId

Upvotes: 3

Related Questions