query on multiple tables

[aletrento]

I have a database with several tables. Each of them (named after a user) contain a list of events with a time length. I can access each table individually and compute all the information I need.

I have been trying to query all the tables together (for instance to get average length of events among all the users), but it seems that the JOIN function works with two tables. I have many of them. Any ideas?

Each tale has the same structure - one event per row (here is a simplified description):

id | start time | end time | description of event | tag

What I need to do is to get the average length of the same type of event (events with the same tag) on a number of tables (each table includes events of a specific user, and new users can register and add their events)

[JimL]

Could you add the table names? Sound like you could improve your db schema a lot.

1. sounds strange that you have tables named after users

2. multiple tables with the exact same columns doesn’t make sense

[JimL]

It sounds like you have this

[code]users
id | name | etc

aletrento_events
id | start time | end time | description of event | tag

JimL_events
id | start time | end time | description of event | tag[/code]

This is not very good db design. As a general convention remember if you’re ever repeating stuff/code, then you are probably doing something wrong (read up on DRY)

[hr]

consider this instead

users id | name | etc | ---- foreign key relation | events id | user_id | start time | end time | description of event | tag

Now we can use one events table for the events belonging to all the users. To get the latest events you can do

SELECT * FROM events ORDER BY id DESC LIMIT 0,10

To get the latest events belonging to one user you do

SELECT * FROM events WHERE user_id = ? ORDER BY id DESC LIMIT 0,10

etc

[benanamen]

What [member=71845]JimL[/member] said. You have a very bad database design. It would be wise to learn about database normalization.