MySQL column not found

The SQL query without where statement runs great开发者_StackOverflow and outputs good results, but when I include WHERE condition it shows Unknown column 'date1' in 'where clause'. What's the problem?

SELECT
  IF( e.weekly,
      DATE_ADD(DATE(e.time),
      INTERVAL CEIL(DATEDIFF('2010-04-08', e.time)/7) WEEK ),
   DATE(e.time)) AS `e.date1`,
   `v`.`lat`,
   `v`.`lng` 
FROM `events` AS `e`
INNER JOIN `venues` AS `v` ON e.venue_id = v.id
WHERE e.date1 > '2010-09-01'

You cannot alias a column with <tablename>.<name>. Instead of AS e.date1 you really have to use AS date1. (If you omit the backticks to create the alias you will get an SQL syntax error.)

But this is only one reason. The other is that aliases can't be used in WHERE clauses.

From the documentation:

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column

and

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

But you can use aliases in a HAVING clause:

SELECT
  IF( e.weekly,
    DATE_ADD(DATE(e.time),
    INTERVAL CEIL(DATEDIFF('2010-04-08', e.time)/7) WEEK ),
    DATE(e.time)) AS `date1`,
  `v`.`lat`,
  `v`.`lng` 
FROM `events` AS `e`
INNER JOIN `venues` AS `v` ON e.venue_id = v.id
HAVING date1 > '2010-09-01'

or you repeat the whole IF statement in the where clause.

A debugging question for you, what happens if you have a simple Select where you Select e.date1 from 'events' as 'e' ? I usually pull back to that type of query in case for some reason my syntax error is being hidden by something.

By the way, I noticed that you have 'e.date1' and then use e.date1, i.e. no single quotes. That would seem to not work, though I am not a MySQL guy.

Believe it or not, it appears you need to add the same IF statement in the where clause.

SELECT
  IF( e.weekly,
      DATE_ADD(DATE(e.time),
      INTERVAL CEIL(DATEDIFF('2010-04-08', e.time)/7) WEEK ),
   DATE(e.time)) AS `e.date1`,
   `v`.`lat`,
   `v`.`lng` 
FROM `events` AS `e`
INNER JOIN `venues` AS `v` ON e.venue_id = v.id
WHERE IF( e.weekly,
      DATE_ADD(DATE(e.time),
      INTERVAL CEIL(DATEDIFF('2010-04-08', e.time)/7) WEEK ),
   DATE(e.time)) > '2010-09-01'

edit: the error message is »Unknown column 'date1' in 'where clause'«

the reason for this is, that the op aliases the if statement to the alias-name 'e.date1'. in his where clause he writes e.date1 without any back ticks. e.date1 will look for a column date1 in table e, and is different from the alias 'e.date1' (with back ticks)

you are aliasing the subquery to the name "e.date1" and then reference the column "date1" in the table "e"—which does not exist.

you have to either alias to "date1" and then use WHERE date1 or write the where as WHERE ``e.date1`` (messed up by markdown...

full code:

SELECT
  IF( e.weekly,
      DATE_ADD(DATE(e.time),
      INTERVAL CEIL(DATEDIFF('2010-04-08', e.time)/7) WEEK ),
   DATE(e.time)) AS `e.date1`,
   `v`.`lat`,
   `v`.`lng` 
FROM `events` AS `e`
INNER JOIN `venues` AS `v` ON e.venue_id = v.id
WHERE `e.date1` > '2010-09-01'

or

SELECT
  IF( e.weekly,
      DATE_ADD(DATE(e.time),
      INTERVAL CEIL(DATEDIFF('2010-04-08', e.time)/7) WEEK ),
   DATE(e.time)) AS e.date1,
   `v`.`lat`,
   `v`.`lng` 
FROM `events` AS `e`
INNER JOIN `venues` AS `v` ON e.venue_id = v.id
WHERE e.date1 > '2010-09-01'