IF query in QB?

[mistel]

Hi everyone,
I am at this forum hoping to get some assistance in solving my problem. Actually, I have a table 'results' with soccer results:

game_number team1 team2 goal1 goal2
--------------------------------------------
1 A B 3 3
2 C D 4 2
3 B C 1 4
4 D A 0 0


Then tried to make a SELECT in MySQL QB, where I need into the new selected table 'points' the following information:

team1, team2, point1, point2

SELECT team1, team2, point1, point2 FROM results
WHERE ....

I need the points for both teams and they are calculated like this:

if results.goal1 - results.goal2 > 0 then point1 = 3 and point2 = 0
if results.goal1 - results.goal2 = 0 then point1 = 1 and point2 = 1
if results.goal1 - results.goal2 < 0 then point1 = 0 and point2 = 3

So if team1 makes more goals than team2, then team1 gets 3 points and team2 0 -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[Thalian]

I guess trying the following would do for you:

SELECT team1, team2,
CASE
WHEN goal1 - goal2 > 0 THEN 3
WHEN goal1 - goal2 = 0 THEN 1
WHEN goal1 - goal2 < 0 THEN 0
END AS 'point1',
CASE
WHEN goal1 - goal2 > 0 THEN 0
WHEN goal1 - goal2 = 0 THEN 1
WHEN goal1 - goal2 < 0 THEN 3
END AS 'point2'
FROM 'results'; -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --