[SOLVED] Combine two subselect over different databases without reference

Combine two subselect over different databases without reference

I need to combine two subselects into one sortable result. The data comes from two databases on the same server.

So for example I have these two queries:

Query #1

SELECT foo.someId AS id, foo.name AS title, foo.tstmp AS timestamp, 'db1' as db
FROM `db1`.`table_foo` AS `foo`
ORDER BY foo.tstmp DESC

Query #2

SELECT bar.that_id AS id, bar.object_title AS title, bar.timestamp AS timestamp, 'db2' as db
FROM `db2`.`bar` AS `bar`
ORDER BY bar.timestamp DESC

Now I want to somehow join these two selects into one result and use LIMIT with an offset to paginate over the result.

Is this even possible? There is no table over which to join the two subselects.

Solution

Try using UNION ALL this:

(
SELECT foo.someId AS id, foo.name AS title, foo.tstmp AS timestamp, 'db1' as db
FROM `db1`.`table_foo` AS `foo`
) UNION All (
SELECT bar.that_id AS id, bar.object_title AS title, bar.timestamp AS timestamp, 'db2' as db
FROM `db2`.`bar` AS `bar`
)
ORDER BY 3 DESC
LIMIT N,M

Without the optional ALL, duplicate entries would be stripped. You might not need it if you can rule our duplicates.