[thelist] MySQL Select from multiple tables - have I got it right?
Chris Dempsey
evolt at cubeit.co.uk
Wed May 16 06:13:17 CDT 2007
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?
More information about the thelist
mailing list