开发者

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 th开发者_C百科is 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?


   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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜