Table joins

hey,

I have seven different tables that i need to join together and order in ascending order by a colum named 'real_date'. Each table is set up in the same format, but holds different data. The first table is named '1_dates' and the second '2_dates'. The other five follow the same pattern. This is the SQL statement:

select * from 1_dates, 2_dates, 3_dates, 4_dates, 5_dates, 6_dates, 7_dates ORDER BY `real_date` ASC

When I try this query I recieve this error:

MySQL said:
Column: 'real_date' in order clause is ambiguous

Does anyone know what the problem is, and how i can fix it?

Thanks

If these errors appear, there is something seriously wrong with the normalisation...

I would say that in your query you have to identify the real_date. If your tables have identical structure and each have the real_date column, MySQL simple don't know which one to use.

select * from 1_dates, 2_dates, 3_dates, 4_dates, 5_dates, 6_dates, 7_dates ORDER BY 1_dates.real_date ASC;

If it is in first 1_dates table.

When I try the above mySQL generates a huge table. I think I might be going about this the wrong way. What I need is all the dates from each table listed together in ascending order. Each table has a colum named 'real_date' that holds a date vaule in yyyy-mm-dd form. Im wanting mySQL to be able to create a single table that has only one colum which will show all the dates from all the tables in ascending order.

Does anyone know if this is possible, or how to do it?

Thanks

Why do you have 7 different tables, with the same structure? :s

Yeah that's a major problem.

And seems stupid to me...

Can't you normalize the database structure? Is a lot more effective

What you're looking for is more along the lines of a union, not a join. UNION is only available in MySQL 4.0.0 and up.