[thelist] MySQL Select from multiple tables - have I got it right?

[Chris Dempsey]

Hi,

I'm working on a database that contains 5 events tables - one table for each
category of event.  The tables are identical in structure and contain fields
for:

	Story_id
	Entity_id
	Title
	Description
	Date_from
	Date_to
	Times
	Contact
	Booking

I need a select statement to pull out all the events in the database across
the 5 tables [I'll then add some WHERE clauses to filter the results
dependant on user requirement].

I looked at merge, join and union options but they confused the heck out of
me then I tried something that just looks too simple to be a correct and
efficient answer:

	SELECT * FROM tbl_1
	UNION ALL
	SELECT * FROM tbl_2
	UNION ALL tbl_3
	UNION ALL
	SELECT * FROM tbl_4
	UNION ALL
	SELECT * FROM tbl_5
	ORDER BY date_from ASC
 
Is there any reason why this would not work either in terms of the select
statement getting confused and missing out some records or in terms of being
inefficient and causing high database load?

Kind regards,
 
Chris Dempsey

ps. If it was me that created the database I'd have used a single table and
added a field for 'event_category' rather than having 5 individual tables.
Would this have been a better way to store the event information or would an
answer to this be dependant on a host of other questions?