MySQL select from multiple tables

[ezkimo]

Hi,
I'm having some trouble figuring out how to do this, it seems like it should be easier than I'm making it.

I have a bunch of tables that are identical in structure and all have a field called post_date (formatted 2010-05-24 15:42:25) which is the date the row was entered and an auto-incremented ID, but each table has a different numbers of entries.

I want to be able to select the the newest 10 entries from all the tables by post_date. So that could mean the results are returned as a row from table 1, then from table 4, then from table 1 again, then table 8, etc etc because it's based on post date.

Any idea how to go about this?!

Thanks!

 

[stomer]

Select * from (
select * from table1
order by post_date
limit 10
union 
select * from table2
order by post_date
limit 10
union 
select * from table3
order by post_date
limit 10
union
select * from table4
order by post_date
limit 10
) order by post_date limit 10

Something like that?

 

[ezkimo]

Thank you! Not sure why I was making it so complicated.

I ended up using this because I was getting some mysql errors:

SELECT * FROM (
(SELECT * FROM 14_posts 
ORDER BY ID DESC
LIMIT 10)
UNION 
(SELECT * FROM 15_posts 
ORDER BY ID DESC
LIMIT 10)
UNION 
(SELECT * FROM 16_posts 
ORDER BY ID DESC
LIMIT 10)
UNION
(SELECT * FROM 17_posts 
ORDER BY ID DESC
LIMIT 10)
UNION
(SELECT * FROM 18_posts
ORDER BY ID DESC
LIMIT 10 )
) AS Tmp ORDER BY post_date DESC LIMIT 10