Unanswered: How to join tables with same structure
I have multiple tables, table_a, table_b, table_c. Each having the same structure. i.e ID, title, rating, content. I'm using
(select * from table_a order by rating DESC) union (select * from table_b order by rating DESC) union (select * from table_c order by rating DESC) to list all titles from a,b,c tables. But it only lists tables one after the other.
How can I list top ten rated titles from all the tables.
ORDER BY rating DESC
But you should really re-think your table design.
A lot of tables with the same base name, but with a different incrementing suffix that all have the same structure sure sounds like bad design.
Your current problem is just one proof that this is not right
Thank you very much, I'll try out your suggestion. I have more than 500,000 items arranged into different tables alphabetically. It might be too heavy to fill all of 'em in a single table. And even the query would be too slow. What do you suggest?