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 = 'bla@blah.com'
AND DateTime > NOW( ) - INTERVAL 30 DAY
I know I need to add Aliases but I'm not sure where
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 = 'bla@blah.com'
AND DateTime > NOW( ) - INTERVAL 30 DAY
UNION ALL
SELECT DateTime
FROM Book_Searches
WHERE User_Email = 'bla@blah.com'
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 = 'bla@blah.com'
AND DateTime > NOW( ) - INTERVAL 30 DAY
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 = 'bla@blah.com'
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 = 'bla@blah.com'
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.
