I tried to return some of the rows in table service
:
SELECT ps.post_name as post, c.cname, p.place, s.date_from,
s.date_to, e.emp_name as employee, s.court, s.place
FROM `service` s
INNER JOIN `employee` e ON s.empno = e.empno
INNER JOIN `posts` ps ON s.post = ps.code
INNER JOIN `court` c ON s.court = c.code
INNER JOIN `place` p ON s.place = p.code
WHERE s.court=13 AND s.place=7 AND s.date_to='0000-00-00'
ORDER BY s.post
This works with results as expected with various values. But when I remove s.court=13
and s.place=7
and have WHERE s.date_to='0000-00-00'
it fails and shows all the records in service
. I tried various ways, including with inner join and left join, but in vain.
What is wrong?
2
Answers
It’s hard to know the exact problem, it would be better if u post some screenshots of the results. But, looking at what you provided, maybe it can be something related to s.date_to column data type. Try checking the data type and if necessary casting it to date, and after that apply the filtering clause again.
please check your sql_mode: SELECT @@sql_mode;
especially for NO_ZERO_DATE, which affects how MySQL interprets ‘0000-00-00’ dates
please Change ‘0000 00 00’ to other non zero data