MySQL condition on the COALESCE syntax

I have a query composing of COALESCE synta开发者_运维百科x. the COALESCE syntax simply find the Audited Date of a file. On my WHERE statement, I just want to output the Audited File base from the AuditDate alias field but i doesn't work. Here is my query.

SELECT COALESCE(qua.starttime, prd.starttime) AS `AuditDate`,
       prd.employeeno AS `EmployeeNo`,
       prd.starttime AS `StartTime`,
       prd.endtime AS `EndTime`
  FROM production prd
  LEFT JOIN qualityaudit qua
    ON prd.id=qua.id
 WHERE `AuditDate` BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

Can anyone help me on how should I get this query correct?

Your query need to be like this

SELECT COALESCE(qua.starttime, prd.starttime) AS `AuditDate`,
       prd.employeeno AS `EmployeeNo`,
       prd.starttime AS `StartTime`,
       prd.endtime AS `EndTime`
  FROM production prd
  LEFT JOIN qualityaudit qua
    ON prd.id=qua.id
 WHERE COALESCE(qua.starttime, prd.starttime) BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

for your situation you can't use the alias that came from a result of a function as query criteria in WHERE clause (see http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html). But you can use the alias to search in HAVING clause, as MySQL 5.0.2 and up permit HAVING clause to refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions, like

SELECT COALESCE(qua.starttime, prd.starttime) AS `AuditDate`,
       prd.employeeno AS `EmployeeNo`,
       prd.starttime AS `StartTime`,
       prd.endtime AS `EndTime`
  FROM production prd
  LEFT JOIN qualityaudit qua
    ON prd.id=qua.id
 HAVING `AuditDate` BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

both have same result

use

SELECT COALESCE(qua.starttime, prd.starttime) AS `AuditDate`,
       prd.employeeno AS `EmployeeNo`,
       prd.starttime AS `StartTime`,
       prd.endtime AS `EndTime`
  FROM production prd
  LEFT JOIN qualityaudit qua
    ON prd.id=qua.id
 WHERE COALESCE(qua.starttime, prd.starttime) BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

The above assumes that your date format/column types really match...

You cannot use an alias as others have pointed out. To avoid calling coalesce two times, you can use a HAVING statement.

SELECT COALESCE(qua.starttime, prd.starttime) AS `AuditDate`,
       prd.employeeno AS `EmployeeNo`,
       prd.starttime AS `StartTime`,
       prd.endtime AS `EndTime`
  FROM production prd
  LEFT JOIN qualityaudit qua
    ON prd.id=qua.id
HAVING `AuditDate` BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

You can't use an alias from the SELECT list in the WHERE clause.

Use this:

SELECT *
FROM
  ( SELECT COALESCE(qua.starttime, prd.starttime) AS `AuditDate`,
           prd.employeeno AS `EmployeeNo`,
           prd.starttime AS `StartTime`,
           prd.endtime AS `EndTime`
      FROM production prd
      LEFT JOIN qualityaudit qua
        ON prd.id=qua.id
  ) AS tmp
WHERE `AuditDate` BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';

or duplicate the code:

SELECT COALESCE(qua.starttime, prd.starttime) AS `AuditDate`,
       prd.employeeno AS `EmployeeNo`,
       prd.starttime AS `StartTime`,
       prd.endtime AS `EndTime`
  FROM production prd
  LEFT JOIN qualityaudit qua
    ON prd.id=qua.id
 WHERE COALESCE(qua.starttime, prd.starttime) 
         BETWEEN '2011-10-01 00:00:00' AND '2011-10-01 23:59:59';