CODEWITHSUNDEEP
mysql
Rizwan Saleem
Rizwan Saleem

Reputation: 896

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

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?

Upvotes: 5

Views: 62

Answers (4)

Rick James
Rick James

Reputation: 142296

Neither of the UNION Answers does the LIMIT 180, 20 optimally for the general case.

( SELECT ... ORDER BY .. LIMIT 200 )
UNION ALL
( SELECT ... ORDER BY .. LIMIT 200 )
ORDER BY .. LIMIT 180, 20

This will get the 'right' 20 rows regardless of whether either SELECT finds less than, or more than, 200 rows.

(Note, the '200' comes from '180+20'.)

Upvotes: 0

Terrible Coder
Terrible Coder

Reputation: 980

You can try this query

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)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

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;

Upvotes: 2

sagi
sagi

Reputation: 40481

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

Upvotes: 1

Related Questions