How to use an alias of table declared in 'FROM' in 'WHERE' clause?

Question

My tables are: t1(name, date, sold); t2(name, date, bought);

My query is:

SELECT *
FROM (SELECT * FROM t1
      UNION ALL 
      SELECT * FROM t2) AS tRes
WHERE
    date < DATE_ADD( DATE_ADD( (SELECT MAX(date) FROM tRes), INTERVAL -DAY((SELECT MAX(date) FROM tRes)) + 1 DAY), INTERVAL -2 MONTH );

I.e. I'm trying to receive rows with dates which are less on two months than max date in tRes - when 2007-12-03 is the max date in the union, the result dates should be less than 2007-10-01.

I'm getting an error 'tRes does not exist' (1146). It's simplified version of the query, in fact there's a lot of code in tRes declaration so I cannot just copy the declaration in selects's below. What can I do here? I want to declare alias of the union which is visible in 'WHERE' clause.

Possible duplicate of [Using column alias in WHERE clause of MySQL query produces an error](http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) — PM 77-1, Dec 23 '16 at 15:44

score 1 · Accepted Answer · answered Dec 23 '16 at 15:45

Yes, that is correct. An alias is defined in the FROM is not available for another FROM clause. So, one solution is to repeat the logic:

SELECT *
FROM (SELECT * FROM t1
      UNION ALL 
      SELECT * FROM t2
     ) tRes CROSS JOIN
     (SELECT MAX(date) as maxdate
      FROM (SELECT * FROM t1
            UNION ALL 
            SELECT * FROM t2
           ) tRes
     ) tresmax
WHERE date < DATE_ADD(DATE_ADD(tResmax.maxdate, INTERVAL - DAY(tResmax.maxdate + 1 DAY
                              ), INTERVAL -2 MONTH
                     );

How to use an alias of table declared in 'FROM' in 'WHERE' clause?

1 Answers1