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
精彩评论