Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
Thank you! Very nice idea - I'm testing it now. –  Ivanhoe123 Nov 11 '13 at 18:53
    
Works perfectly. Thanks again. –  Ivanhoe123 Nov 11 '13 at 19:46
1  
Almost perfectly. The potential pitfall here is that this often scales poorly since UNION causes views to be rendered with the TEMPTABLE algorithm which means the result of the SELECT inside the view is fetched into an implicit temporary table, and then any WHERE clause applied to the outer SELECT from the view is applied to the temporary table, removing the ability to use indexes to satisfy the WHERE clause used against the view (only WHERE clauses that are part of the view definition itself can still use indexes). –  Michael - sqlbot Nov 12 '13 at 1:07
    
@Michael-sqlbot thanks for the heads up. Is there anything I can do to avoid this? Is there any other alternative? –  Ivanhoe123 Nov 12 '13 at 9:16
1  
@Ivanhoe123 potentially, but it will depend on the nature of the restrictions in your CMS interface; if the view, itself, returns a small result and you don't need an outer WHERE then the view is no worse than the original query, which most likely will create a temp table anyway, since table indexes can't be used for sorting the result of UNION. –  Michael - sqlbot Nov 12 '13 at 12:30
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.