How to fetch record from another table if first table return no record using UNION in MySQL query

Question

I have two table name 'activites' & 'archived_activities'. I devide my activities table record into another table record. Activities table contain only first 200 latest activities of users and remaining record moved to archived_activities table. Now I want to join both table only when activities table return null then I want to use same offset and limit for archived_activities table to fetch next record. Below I my query that is not working fine.

SELECT * FROM activities WHERE user_id=87 LIMIT 180,20
UNION ALL 
SELECT * FROM activities WHERE user_id=87 LIMIT 180,20

But this query working not fine.

Any help?

Answer 1

One approach here would be to do a union to get both current and archived records into one logical table, but to order them such that current records get higher priority, should they exist. I assign a position of 1 to current records and 2 to archived records. Then, I order by this position and retain 200 records.

SELECT col1, col2, ...
FROM
(
    SELECT col1, col2, ..., 1 AS position
    FROM activities
    WHERE user_id = 87
    UNION ALL
    SELECT col1, col2, ..., 2
    FROM archived_activities
    WHERE user_id = 87
) t
ORDER BY
    position
LIMIT 200;