MYSQL Union All with aliases

alias, mysql, union

Solution

The error is pretty clear, add an alias to that table:

SELECT * 
FROM 
(
  SELECT app_id FROM apps
  UNION ALL 
  SELECT app_id FROM reviews
)   AS t -- < -------------- this
GROUP BY app_id 
HAVING COUNT(*) = 1;

Note that: The `GROUP BY` with `SELECT *` is not recommended and it is not complied with ANSI SQL. It will work fine in mysql, but try not to put columns in the `SELECT` clause that are not in the `GROUP BY` nor in an aggregate functions. MySQL will get an arbitrary values for those columns in your case.

Problem

I am trying to find all the applications that exist in the app table that do not yet have a review in the review table. ``` SELECT * FROM ( SELECT app_id FROM apps UNION ALL SELECT app_id FROM reviews ) GROUP BY app_id HAVING COUNT(*) = 1 ``` After finding which applications do not have a review, I would like to have all the values from the app table in order to list out the applications that still need a review. The app_id corresponds at the key for the application information in the apps table and when an application is reviewed, the app_id is then entered into the reviews table under app_id along with comments and the rankings in their respected fields. I am getting this error and can't seem to figure out how to correctly add the aliases. Every derived table must have its own alias

Original source