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

[Chris Dempsey]

Jason,

Very fast response - thank you!

Yeah, tbl_3 part is a typo.

Think it's time to consider changing the database structure to a single
table as the database is shared by around 100 websites at the moment.  Issue
is that several of these sites have their own admin panel for the database
and the more of these that are out there the more difficult it'll be to
change the structure in future.

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Jason Handby
Sent: 16 May 2007 12:25
To: thelist at lists.evolt.org
Subject: Re: [thelist] MySQL Select from multiple tables - have I got
itright?

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

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt !