LinuxQuestions.org - Selecting data from multiple mysql tables

- Programming (https://www.linuxquestions.org/questions/programming-9/)

- - Selecting data from multiple mysql tables (https://www.linuxquestions.org/questions/programming-9/selecting-data-from-multiple-mysql-tables-591563/)

Selecting data from multiple mysql tables

So I have multiple tables with identical layouts. They are full of statistics about users' activity in different locations. Each table is for a different location. This makes it very easy to show statistics for a particular location (table) but very difficult to show a user's statistics across tables. My old way of doing it was as follows:

Code:

For each table listed in SHOW TABLES:

    select the data from that table where the user's ID is XYZ

    print the data

The problem is that I can't sort the data. In other words, I can't have it order the data by the most "active" locations to the least active locations.

Another issue I am having with these tables is as follows: I would like to get the sum of each column in each table (since they're all ints) to give me a single row of sums. Then I would like to be able to sort the sum rows of each of those tables by the values of certain columns (like the problem above).

Can anyone help me with these two problems? If you need clarification or table structures I can post them.

I think that your design (table per location) is inherently flawed.
Put all data into one table that holds 1 extra column for the location.

That will still make for easy retrieval per location

Code:

select col1,col2, ... from table where location=x

, and all the
aggregation and sorting you might want, too.

Cheers,
Tink

I think Tinkster is right about your table design. But if you need a short term solution, mysql 5 supports the union statement to join (and order) multiple select statements. Have a look at http://dev.mysql.com/doc/refman/5.0/en/union.html - I've had some problems with execution times using multiple union statements, but maybe that's just me...

2 problem:

Or you can

select sum(column1) as "col1", sum(column2) as "col2", etc... from tb1
select sum(column1) as "col1", sum(column2) as "col2", etc... from tb2
select sum(column1) as "col1", sum(column2) as "col2", etc... from tb3
........

and create new table from this data, then sort it how you like.