[MYSQL] - Select from several identical tables

[makko]

I got several tables that are identical as structure. I need to select all the records from those tables,
filtered and ordered by date column .

So far no problem :

(SELECT * FROM table1 WHERE (Category Like '%Category')) UNION
(SELECT * FROM table2 WHERE (Category Like '%Category')) UNION
(SELECT * FROM table3 WHERE (Category Like '%Category')) UNION
(SELECT * FROM table4 WHERE (Category Like '%Category')) UNION
(SELECT * FROM table5 WHERE (Category Like '%Category')) ORDER BY Data

I want my records to be sorted by date as you see in the querry above ... but ... if there are several records
from table1 and table5 from the same day, it seems that MYSQL will return first the table1 records and after that
table5 records, even if some table5 records are newer.

for example
table1
1 aaa 2008-01-22
2 bbb 2008-01-21

table5
1 ccc 2008-01-27
2 ddd 2008-01-10

The correct result should be
2 ddd 2008-01-10
2 bbb 2008-01-21
1 aaa 2008-01-22
1 ccc 2008-01-27

The result I get
2 bbb 2008-01-21
1 aaa 2008-01-22
2 ddd 2008-01-10
1 ccc 2008-01-27

I tried the querry in SQL SERVER and I get the correct result every time

Where is my mistake ? or what can I change to get the correct result without puting the querry into a view or
using just one table containing all the records

[si_the_geek]

Try putting brackets around everything apart from the Order By (ie: add an open bracket at the very start, and a close bracket just before Order). Note that there is no need for your existing brackets, but if you want them (for readability), then that is up to you.

Is there a particular reason you are using separate tables, rather than one (perhaps with an extra field to specify which kind of data each row is)?