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

[Jason Handby]

Hi Chris,


> 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?

I think this is the best option, mainly because it's the only option :-)

Incidentally your syntax goes a little off around "tbl_3" but I'm assuming that's a typo.


> 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?

I would say it's almost certainly a better way. Advantages would include query performance, and ease of adding new event categories in the future. Disadvantages? well, all your events would have to have a common data structure. But it sounds like they do.




Jason