I need to combine the results from two queries into one result set and order them altogether.
This is the working, raw SQL code I have:
SELECT * FROM (
SELECT
t1.id AS entity_id,
t2.title,
'query1' AS entity_type
FROM
`table1` t1,
`table2` t2
WHERE
t1.id = t2.id
UNION ALL
SELECT
t3.id AS entity_id,
t4.title,
'query2' AS entity_type
FROM
`table3` t3,
`table4` t4
WHERE
t3.id = t4.id
) AS results ORDER BY results.entity_id
The query above works perfectly, but I need to convert it into a specific syntax for a CMS database API, which does not support such queries.
I am looking for something without the SELECT * FROM(subquery)
as such queries can be converted easily.
Is there any command I can use to UNION ALL
and order records from both sub-queries?
Thank you!
Best Answer
I would consider taking the union queries and make a view using them. You can then treat the union as if it was a table.