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

Ken Moore psm2713 at hotmail.com
Wed May 16 13:09:35 CDT 2007

Hi all,

The most direct solution is to pre-process with a make table query that 
combines the 5 tables with info about which table each row came from. Then 
do your query.

Then start lobbying to get the structure changed.


>From: "Chris Dempsey" wrote
>I'm working on a database that contains 5 events tables - one table for 
>category of event.  The tables are identical in structure and contain 
>	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
>	SELECT * FROM tbl_2
>	UNION ALL tbl_3
>	SELECT * FROM tbl_4
>	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 
>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 
>answer to this be dependant on a host of other questions?

Make every IM count. Download Messenger and join the i’m Initiative now. 
It’s free. http://im.live.com/messenger/im/home/?source=TAGHM_MAY07

More information about the thelist mailing list