Share your knowledge at the LQ Wiki.
Go Back > Forums > Non-*NIX Forums > Programming
User Name
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.


  Search this Thread
Old 10-13-2007, 04:26 PM   #1
Registered: Aug 2004
Location: Ohio
Distribution: Debian, Slackware
Posts: 58

Rep: Reputation: 15
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:
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.
Old 10-13-2007, 08:16 PM   #2
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
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
select col1,col2, ... from table where location=x
, and all the
aggregation and sorting you might want, too.

Old 10-13-2007, 09:46 PM   #3
Senior Member
Registered: Dec 2005
Location: Brisbane, Australia
Distribution: Slackware64 14.0
Posts: 4,125

Rep: Reputation: 164Reputation: 164
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 - I've had some problems with execution times using multiple union statements, but maybe that's just me...
Old 10-14-2007, 07:00 AM   #4
Senior Member
Registered: Oct 2005
Location: Lithuania
Distribution: Hybrid
Posts: 2,247

Rep: Reputation: 53
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.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Wierd SQL query selecting from table acording another tables lack of values? matthewhardwick Programming 3 07-30-2007 05:51 AM
selecting records from multiple files in mysql question MRMadhav Programming 7 11-05-2006 04:52 PM
Dynamically select data from MySQL tables tangle Programming 6 02-28-2006 05:48 PM
mysql reinstallation distorted by previous tables in /var/mysql mad4linux Linux - Software 0 10-04-2005 01:39 PM
UPDATE multiple mySQL tables gruessle Programming 1 02-03-2004 07:41 AM

All times are GMT -5. The time now is 12:34 AM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration