Member Avatar for 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.

  • 5 Contributors
  • 4 Replies
  • 102 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by ebookfinder
Member Avatar for 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

Member Avatar for 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
Member Avatar for 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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.