mysql multiple conditions in if statement

Question

New to mysql, so I am not sure I am even asking the question correctly. I am trying to add multiple parameters to an if statement. I am trying to check if a game between two teams was a league match.

SELECT visitor AS school, home AS temp, vl.leagueid AS vleague, hl.leagueid AS hleague, 
if(vl.leagueid = hl.leagueid, 1, 0) AS leaguematch
FROM schedule 
LEFT JOIN schools AS vl ON vl.id = visitor 
LEFT JOIN schools AS hl ON hl.id = home
WHERE gamedate between '2013-01-01' AND '2013-12-31'

To the check if leagueid's are equal I want to add ((vl.leagueid = 26 AND hl.leagueid = 27) OR (vl.leagueid = 27 AND hl.leagueid = 26)). No combinations of () that I have tried seems to work, so I get the feeling I am going about this wrong.

Thanks, Mike

Thanks to chofer, here is my working query

SELECT visitor AS school, home AS temp, vl.leagueid AS vleague, hl.leagueid AS hleague, 
CASE 
    WHEN vl.leagueid = hl.leagueid THEN '1' 
    WHEN ((vl.leagueid = 26 AND hl.leagueid = 27) OR (vl.leagueid = 27 AND hl.leagueid = 26)) THEN '1' 
    ELSE 0 
END AS leaguematch

FROM u96nk_rvball_schedule 
LEFT JOIN u96nk_rvball_schools AS vl ON vl.id = visitor 
LEFT JOIN u96nk_rvball_schools AS hl ON hl.id = home
WHERE gamedate between '2013-01-01' AND '2013-12-31'

chofer · Accepted Answer · 2014-05-20 15:27:52Z

3

if you need vl.leagueid and hl.leagueid are equal put the condition on where, like this

WHERE gamedate between '2013-01-01' AND '2013-12-31' AND vl.leagueid = hl.leagueid

EDIT If you want to set leaguematch to specific value if the conditions are met, 0 if they are not CASE would be the best option

SELECT visitor AS school,
       home AS temp,
       vl.leagueid AS vleague,
       hl.leagueid AS hleague,
       CASE 
          WHEN school = 50 AND temp = 2 THEN '1' 
          WHEN school = 51 AND temp = 3 THEN '2' 
          ELSE 0 
       END AS leaguematch
FROM ......

I

edited May 20, 2014 at 15:27

answered May 20, 2014 at 13:33

chofer

3571 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike H Over a year ago

Sorry, probably didn't explain well enough - bad habit - I want to set leaguematch to 1 if the conditions are met, 0 if they are not. So I would a record that would end up looking like this school = 50 temp = 2 vleague = 25 hleague = 25 leaguematch = 1

chofer Over a year ago

Look at the edit, I think a CASE statement would be good for you.

Collectives™ on Stack Overflow

mysql multiple conditions in if statement

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related