[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.

Ken

>From: "Chris Dempsey" wrote
>
>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?
>

_________________________________________________________________
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