How to join tables with same structure

[flashyflashy]

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.

[pritaeas]

add limit 10 to each query

[shasank]

select a.title, b.title, c.title from table_a a, table_b b, table_c c where a.rating > 10 and b.rating > 10 and c.rating > 10;

....

Hope this helps you....
Shasank

[cgyrob]

I know this thread is a few days old but if you want the top 10 from the combined list you just sort the list after the unions not for each table.

(SELECT * from table_a )
union (SELECT * FROM table_b)  
union (SELECT * FROM table_c ) 
order by rating DESC
Limit 10

[ebookfinder]

I know this thread is a few days old but if you want the top 10 from the combined list you just sort the list after the unions not for each table.

(SELECT * from table_a )
union (SELECT * FROM table_b)  
union (SELECT * FROM table_c ) 
order by rating DESC
Limit 10

great!