query multiple tables with same field names

I have eight tables that have the same field names (timestamp, user_count), but hold distinct data.  How can I select the timestamp field in just one table (say, node_j_data) and the user_count field in all tables (e.g. node_a_data, node_b_data, node_c_data and so on...) as seperate entities, ie alias as the follow code suggests.

Thanks for any help!
<?php require_once('connection/connect.php');
mysql_select_db($database, $connection);
 
$query = "SELECT node_j_data.timestamp AS timestamp, node_a_data.user_count AS users_a, node_b_data.user_count AS users_b, node_c_data.user_count AS users_c, etc...";
$result = mysql_query($query, $connection) or die(mysql_error());
 
 
while($row = mysql_fetch_assoc($result)){  
  $timestamp =  date('d-m-y H:i',$row['timestamp']);
  $users_a =  $row['users_a'];
  $users_b =  $row['users_b'];
  $users_c =  $row['users_c'];
   
  echo "$timestamp;$users_a;$users_b;$users_c\n";
} 
 
?>

Open in new window

peterbrowneAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
racekConnect With a Mentor Commented:
it should be sum not count - if you have only one row per timestamp, you don't need sum :-) but it doesn't matter ... group by at the end...
SELECT t.ts,
            sum(a.user_count) as acount,
            sum(b.user_count) as bcount,
            sum(c.user_count) as ccount,
            sum(d.user_count) as dcount
FROM
(SELECT timestamp AS ts FROM node_a_data
UNION SELECT timestamp FROM node_b_data
UNION SELECT timestamp FROM node_c_data
UNION SELECT timestamp FROM node_d_data ) as t
LEFT JOIN node_a_data a on a.timestamp = t.timestamp
LEFT JOIN node_a_data b on b.timestamp = t.timestamp
LEFT JOIN node_a_data c on c.timestamp = t.timestamp
LEFT JOIN node_a_data d on d.timestamp = t.timestamp
GROUP BY 1;

Open in new window

0
 
racekCommented:

select * from yourtable1
UNION ALL
select * from yourtable2
UNION ALL
select * from yourtable3
order by node_j_data.timestamp

Open in new window

0
 
Sander StadSysteemontwikkelaar, Database AdministratorCommented:
You could do something like this:


SELECT
  j.timestamp AS timestamp,
  a.user_count AS users_a,
  b.user_count AS users_b,
  c.user_count AS users_c
FROM
  node_j_data j,
  node_a_data a,
  node_b_data b,
  node_c_data c
WHERE
  ............

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
peterbrowneAuthor Commented:
I tried the following query, but it times out (not to mention almost killing the server...)

SELECT node_j_data.timestamp AS timefield, node_b_data.user_count AS users_b, node_f_data.user_count AS users_f FROM node_j_data, node_b_data, node_f_data

Open in new window

0
 
Sander StadSysteemontwikkelaar, Database AdministratorCommented:
How many rows do the tables have. The reason the server could timeout is because it has no indexes to search what makes the server go and search every field in the database.

Did you create any indexes on the tables? Assuming you use MySQL you can find more information about indexes in this website: http://dev.mysql.com/doc/refman/5.0/en/create-index.html


0
 
racekCommented:
SELECT node_j_data.timestamp AS timefield,
       node_b_data.user_count AS users_b,
       node_f_data.user_count AS users_f
FROM node_j_data j, node_b_data b, node_f_data f
WHERE j.timestamp = b.timestamp and b.timestamp = f.timestamp
0
 
peterbrowneAuthor Commented:
There are 8 tables...same field names...all data is different (the tables are populated every 15 minutes from 8 different node usage logs) including the timestamps.  Yes, the tables all have indexes and return all data no problem for for each table when the query is specific to a table.  However, I need to have a single query which returns all the data from all 8 tables.  The query must feed 8 different variables for user_count for each table.
0
 
racekCommented:
like this???
SELECT 
(SELECT COUNT(*) FROM node_a_data WHERE timestamps>='2008-01-01' ) AS t1count,
(SELECT COUNT(*) FROM node_b_data WHERE timestamps>='2008-01-01' ) AS t2count,
(SELECT COUNT(*) FROM node_c_data WHERE timestamps>='2008-01-01' ) AS t3count,
(SELECT COUNT(*) FROM node_d_data WHERE timestamps>='2008-01-01' ) AS t4count;

Open in new window

0
 
peterbrowneAuthor Commented:
No, I just want return timestamp (just 10 digit numbers) for all rows in one table and user_count for all trows for all 8 tables (no count involved and no WHERE clause).  E.g.:

"SELECT node_j_data.timestamp AS timestamp, node_a_data.user_count AS users_a, node_b_data.user_count AS users_b, node_c_data.user_count AS users_c, node_d_data.user_count AS users_d, node_e_data.user_count AS users_e,  node_f_data.user_count AS users_f. node_i_data.user_count AS users_i,  node_j_data.user_count AS users_j"

However when I run that query, I get a MySQL error "unknown table node_j_data" etc.  As already stated, querys on individual tables work fine, e.g.

"SELECT * FROM node_j_data"
0
 
peterbrowneAuthor Commented:
To clarify, the tables have 3 columns, eg,:

node_a_data_id

timestamp (which is a 10 digit number, no format)

user_count (i.e. the number of users online for on that server node for that timestamp)
0
 
peterbrowneAuthor Commented:
timestamp for one table needs to go into $timestamp.  Then user_count for each table needs to go into:

$users_a =  $row['users_a'];
$users_b =  $row['users_b];
$users_c  =  $row['users_c'];
$users_d =  $row['users_d'];
$users_e =  $row['users_e'];
$users_f  =  $row['users_f'];
$users_i =  $row['users_i'];
$users_j =  $row['users_j'];

This is done in the while loop as first posted...

0
 
racekCommented:


SELECT t.ts,
            count(a.timestamp) as acount,
            count(b.timestamp) as bcount,
            count(c.timestamp) as ccount,
            count(d.timestamp) as dcount
FROM
(SELECT timestamp AS ts FROM node_a_data
UNION SELECT timestamp FROM node_b_data
UNION SELECT timestamp FROM node_c_data
UNION SELECT timestamp FROM node_d_data ) as t
LEFT JOIN node_a_data a on a.timestamp = t.timestamp
LEFT JOIN node_a_data b on b.timestamp = t.timestamp
LEFT JOIN node_a_data c on c.timestamp = t.timestamp
LEFT JOIN node_a_data d on d.timestamp = t.timestamp;


(SELECT COUNT(*) FROM node_b_data WHERE timestamps>='2008-01-01' ) AS t2count,
(SELECT COUNT(*) FROM node_c_data WHERE timestamps>='2008-01-01' ) AS t3count,
(SELECT COUNT(*) FROM node_d_data WHERE timestamps>='2008-01-01' ) AS t4count;
0
 
racekCommented:

SELECT t.ts,
            count(a.timestamp) as acount,
            count(b.timestamp) as bcount,
            count(c.timestamp) as ccount,
            count(d.timestamp) as dcount
FROM
(SELECT timestamp AS ts FROM node_a_data
UNION SELECT timestamp FROM node_b_data
UNION SELECT timestamp FROM node_c_data
UNION SELECT timestamp FROM node_d_data ) as t
LEFT JOIN node_a_data a on a.timestamp = t.timestamp
LEFT JOIN node_a_data b on b.timestamp = t.timestamp
LEFT JOIN node_a_data c on c.timestamp = t.timestamp
LEFT JOIN node_a_data d on d.timestamp = t.timestamp;

Open in new window

0
 
peterbrowneAuthor Commented:
I think you need to have a look at my posts...
0
 
racekCommented:
try to execute my query and you can understand it :-) result will be exactly what are you asking for: timestamp and count from each table for this timestamp ...

timestamp               acount   bcount   ccount
2008-01-01 15:30        0           55         0
0
 
peterbrowneAuthor Commented:
I don't know why you are looking at count or sum. The data returned needs to be all records as is.  Have a look at the attached node_j_data.txt which is an output from table node_j_data.  This is what the output from the combined table query needs to look like, except where there is, for example:

25-04-09 00:15;78   (this is the timestamp and user_count for one record in node_j_data)

needs to be:

25-04-09 00:15;78;88;102;55;67;78;34;47

which has just the one timestamp from one table and then the user_count column for each of the other tables.

This data is then repeated for all rows in all tables (in the while loop) as you can see in the txt file (except that is just for one table).  See code snippet again for the code used to get the data as per the txt file from node_j_data.


<?php require_once('connection/connect.php');
mysql_select_db($database, $connection);
 
$query = "SELECT * FROM node_j_data";
$result = mysql_query($query, $connection) or die(mysql_error());
 
while($row = mysql_fetch_assoc($result)){  
  $timestamp =  date('d-m-y H:i',$row['timestamp']);
  $users =  $row['user_count'];
 
  echo "$timestamp;$users\n";
} 
?>

Open in new window

0
 
peterbrowneAuthor Commented:
Forgot the txt file...
node-j-data.txt
0
 
peterbrowneAuthor Commented:
OK, I have results in the format that I need with the following code.  However, now I have another problem. The timestamps for node_j_data start several hours before the other other tables timestamp, and none of the tables timestamps would actually be in sinc if overlayed in a graph (which is what I am doing).  

I think that the only answer to this problem is to set the tables in the database back to a common starting point and somehow reset the auto increment...can you tell me how to do this for the 500 points (resetting the auto increment to start again after I delete rows from the beginning of each table so that they start from the same time (or close to it))?

Thanks
<?php require_once('connection/connect.php');
mysql_select_db($database, $connection);
 
$query = "SELECT node_j_data.timestamp AS timefield, node_b_data.user_count AS users_b, node_f_data.user_count AS users_f FROM node_j_data, node_b_data, node_f_data WHERE node_b_data_id = node_j_data_id AND node_f_data_id = node_j_data_id";
$result = mysql_query($query, $connection) or die(mysql_error());
 
while($row = mysql_fetch_assoc($result)){  
  $timestamp =  date('d-m-y H:i',$row['timefield']);
  $users_b =  $row['users_b'];  
  $users_f =  $row['users_f']; 
  echo "$timestamp;$users_b;$users_f\n";
} 
 
?>

Open in new window

0
 
peterbrowneAuthor Commented:
Don't worry worked it out....problem solved
0
 
racekCommented:
If you should provide table saying that timestamp is the unique key, then will be query without disscussion as follows:-)
SELECT t.ts,a.user_count  as acount,b.user_count  as bcount,c.user_count  as ccount,d.user_count ) as dcount
FROM
(SELECT timestamp AS ts FROM node_a_data
UNION SELECT timestamp FROM node_b_data
UNION SELECT timestamp FROM node_c_data
UNION SELECT timestamp FROM node_d_data ) as t
LEFT JOIN node_a_data a on a.timestamp = t.timestamp
LEFT JOIN node_a_data b on b.timestamp = t.timestamp
LEFT JOIN node_a_data c on c.timestamp = t.timestamp
LEFT JOIN node_a_data d on d.timestamp = t.timestamp;

Open in new window

0
 
racekCommented:
Autoincrement can be changed easy with

create table newtab (node_a_data_id int not null auto_increment,
timestamp ... user_count ...);

insert into newtab select 0,timestamp, user_count
 from oldtab order by autincrement;

drop oldtab;
rename netab to oldtab ;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.