List all the directors who directed a 'Comedy' movie in a leap year

user_2986845 0
Viewed 2172

Here i am trying to all director name who directed comedy movie in leap year. I am getting wrong result and that may be because the way i am implementing leap year code is not right. Please help.

SELECT m.Title,p.Name,m.year
FROM Movie m JOIN 
     M_director d
     ON m.MID = d.MID JOIN 
     Person p
     ON d.PID = p.PID JOIN
     M_Genre mg
     ON m.MID = mg.MID JOIN
     Genre g 
     ON g.GID = mg.GID
WHERE g.Name LIKE '%Comedy%' AND m.year%4 = 0 AND m.year % 100 <> 0 OR  m.year % 400 = 0
mysql sql
1 Answers

AND has higher precedence over OR, so when you write:

WHERE g.Name LIKE '%Comedy%'
AND m.year%4 = 0
AND m.year % 100 <> 0
OR  m.year % 400 = 0

This means: name has to contain Comedy AND the year must be divisible by 4 AND the year must not be a multiple of 100. Either that OR the year is divisible by 400 (without any other constraints, so regardless of the name). Use parentheses to explicitly specify precedence:

WHERE g.Name LIKE '%Comedy%'
AND ( m.year%4 = 0
AND m.year % 100 <> 0
OR  m.year % 400 = 0 )
user_1129680
Related