skip to Main Content

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


  1. 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.

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search