[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 im Initiative now.
Its free. http://im.live.com/messenger/im/home/?source=TAGHM_MAY07
More information about the thelist
mailing list