Why UNION and UNION ALL don't work in my code?

May 2, 2024

select name as results
from Users u
RIGHT JOIN MovieRating r on r.user_id = u.user_id
group by r.user_id
order by count(*) desc, name
limit 1
UNION ALL
select title as results
from Movies m
RIGHT JOIN MovieRating r on r.movie_id = m.movie_id
where created_at<'2020-03-01'
group by r.movie_id
order by avg(rating )desc, m.title asc
limit 1

I’m trying to create results table by taking the person name with the most reviews from MovieRating and the film title with highest rating in February, so the results table have to look like this

>Solution :

    SELECT results FROM (
    SELECT name AS results
    FROM Users u
    RIGHT JOIN MovieRating r ON r.user_id = u.user_id
    GROUP BY r.user_id
    ORDER BY COUNT(*) DESC, name
    LIMIT 1
) AS t1
UNION ALL
SELECT results FROM (
    SELECT title AS results
    FROM Movies m
    RIGHT JOIN MovieRating r ON r.movie_id = m.movie_id
    WHERE created_at < '2020-03-01'
    GROUP BY r.movie_id
    ORDER BY AVG(r.rating) DESC, m.title ASC
    LIMIT 1
) AS t2;

I could not test the query, but you can try this.