Every derived table must have its own alias error

Question

I get that error when running the following query:

SELECT MAX( DateTime )
FROM (
(
    SELECT DateTime
    FROM Class_Searches
)
UNION ALL (
    SELECT DateTime
    FROM Book_Searches
)
)
WHERE User_Email = '[email protected]'
AND DateTime > NOW( ) - INTERVAL 30 DAY 

I know I need to add Aliases but I'm not sure where

Answer 1

You need an alias for the subquery, and you need to apply the conditions either to both queries that you union:

SELECT MAX(DateTime)
FROM (

  SELECT DateTime
  FROM Class_Searches
  WHERE User_Email = '[email protected]'
  AND DateTime > NOW( ) - INTERVAL 30 DAY

  UNION ALL

  SELECT DateTime
  FROM Book_Searches
  WHERE User_Email = '[email protected]'
  AND DateTime > NOW( ) - INTERVAL 30 DAY

) AS x

or return data so that you can apply the condition in the outer query:

SELECT MAX(DateTime)
FROM (

  SELECT DateTime, User_Email
  FROM Class_Searches

  UNION ALL

  SELECT DateTime, User_Email
  FROM Book_Searches

) AS x
WHERE User_Email = '[email protected]'
AND DateTime > NOW( ) - INTERVAL 30 DAY

Answer 2

An alias is when you rename something, like SELECT t.time from table t, t is the alias for that table. In this case, you need to give an alias to the tables generated by the subqueries:

SELECT MAX( ut.DateTime )
FROM (
(
    SELECT DateTime
    FROM Class_Searches
) cs
UNION ALL (
    SELECT DateTime
    FROM Book_Searches
) bs
) ut
WHERE User_Email = '[email protected]'
AND ut.DateTime > NOW( ) - INTERVAL 30 DAY 

That still won't work though, because you don't have a User_Email column returned from the UNION. Thus, try:

SELECT MAX( ut.DateTime )
FROM (
(
    SELECT DateTime, User_Email
    FROM Class_Searches
) cs
UNION ALL (
    SELECT DateTime, User_Email
    FROM Book_Searches
) bs
) ut
WHERE ut.User_Email = '[email protected]'
AND ut.DateTime > NOW( ) - INTERVAL 30 DAY

It's possible that that still won't work right because of the UNION syntax, but at least it's a lot closer.