LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
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.

Notices

Reply
 
Search this Thread
Old 10-13-2007, 04:26 PM   #1
derzok
Member
 
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:
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.
 
Old 10-13-2007, 08:16 PM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 22,978
Blog Entries: 11

Rep: Reputation: 879Reputation: 879Reputation: 879Reputation: 879Reputation: 879Reputation: 879Reputation: 879
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
 
Old 10-13-2007, 09:46 PM   #3
gilead
Senior Member
 
Registered: Dec 2005
Location: Brisbane, Australia
Distribution: Slackware64 14.0
Posts: 4,123

Rep: Reputation: 151Reputation: 151
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...
 
Old 10-14-2007, 07:00 AM   #4
Alien_Hominid
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.
 
  


Reply


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 11:25 AM.

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