How to get a record from another table if the first table does not return a record using UNION in a MySQL query

Question

I have two table names "activites" and "archived_activities". I am sharing my action table entry into another table. The action table contains only the first 200 recent user actions, and the remaining record has been moved to the archived_activities table. Now I want to join both tables only when the action table returns null, then I want to use the same offset and limit for the archived_activities table to get the next record. Below is my request, which 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 does not work very well.

Any help?

Answer 1

One approach here is to make a join so that both current and archive entries are in the same logical table, but to organize them so that the current entries get a higher priority if they exist. I assign position 1 for current records and 2 for archived records. Then I order this item and save 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; 

Answer 2

You can use NOT EXISTS() :

 SELECT * FROM activities WHERE user_id=87 LIMIT 180,20 UNION ALL SELECT * FROM archieve_activities WHERE NOT EXISTS(SELECT 1 FROM activities WHERE user_id = 87) AND user_id=87 LIMIT 180,20 

Answer 3

You can try this request

 select * from activities as a union all select * from archived_activities as b where b.user_id not in (select r.user_id from activities as r)