Mysql – How to correlate data between 2 tables

MySQLquery

I have 2 tables that I am dealing with, table 1 has identity information (II), columns ID and Name. Table 2 has data collection (DC) information, InfoId, DCId, data, timestamp. The DC table has time series data for each element in the II Table.

II Table

    id    Name
    0      A
    1      B
    2      C

DC Table 

    id  DCId  data  timestamp
     0    0     5      TS3
     1    0     7      TS1
     0    1     8      TS10
     2    1     3      TS5
     1    1     5      TS4
     0    2     6      TS2

Where TS10 is later than TS1, etc

Currently I do a select on the II table to get all of the data, and then use a while loop to get the data in the second table for a matching Id and the max DCid.

Which results in 

    Name      Data    TS
      A        8     TS10
      B        5     TS4        
      C        3     TS5 

I would like to do this with one select statement because I would like the result ordered by timestamp, but I am unsure how to get what I want.

Name      Data    TS
  A        8     TS10
  C        3     TS5        
  B        5     TS4 

The current code looks similar to this:

Select * from II ;
for ( results )
{
   Select Max(DC.DCId) as MaxDCId from DC where DCId=II.sensorInfoId ;
   Select Data, timestamp from DC where DCId=:MaxDCId AND DC.ID =II.ID
   (Stick results for name, value, and timestamp in HTML table)
}

I am not sure if I need a sub-query join or combinations of both to get what I want (or if it is possible with a single SQL query). I did it this way for expediency…I wanted to get the application running. Now that I want the result table ordered by timestamp, I could cache all the values, sort em and then display the HTML table, but if I could do it with 1 query and let SQL do the work I would rather do that.

Best Answer

SELECT 
   Name,
   SUM(DC.data) AS Data,
   MAX(DC.TS) AS TS
FROM II
INNER JOIN DC ON DC.id = II.id
GROUP BY II.Name
ORDER BY 3 DESC

In stead of ORDER BY 3 DESC, you could also write ORDER BY MAX(DC.TS) DESC.