Joining one table to the latest row in another table using MySQL

I want to join two tables in a special way, first table is devices which has a list of devices.

The second table is datalog which is where abit of data is stored for everytime a device in devices gets polled.

Devices Table:

+----------+------------+----------------------------+---------------------+
| deviceId | deviceName | deviceDescription          | timeCreated         |
+----------+------------+----------------------------+---------------------+
|        1 | System 1   | Main System in Server Room | 2010-01-01 00:00:00 |
|        2 | System 2   | Outdoor System             | 2010-01-01 00:00:00 |
+----------+------------+----------------------------+---------------------+

DataLog Table:

+----+---------------------+----------+-----------+---------+
| id | time_stamp          | DeviceId | FuelLevel | Voltage |
+----+---------------------+----------+-----------+---------+
|  1 | 2010-01-01 00:00:00 |        1 |        60 |     220 |
|  2 | 2010-01-01 00:00:00 |        2 |        20 |     221 |
|  3 | 2010-01-02 00:00:00 |        1 |       100 |     219 |
|  4 | 2010-01-02 00:00:00 |        2 |       100 |     222 |
|  5 | 2010-01-03 00:00:00 |        1 |        80 |     219 |
|  6 | 2010-01-03 00:00:00 |        2 |        99 |     220 |
+----+---------------------+----------+-----------+---------+

Currently I am getting the latest data for each device using a query on the DataLog table with:

 W开发者_如何学Chere DeviceId = 1 ORDER BY timestamp DESC LIMIT 1

What I would like is one query to return a list of all devices, with the columns joined with the latest data for each device like this:

+----------+------------+----------------------------+---------------------+-----------+---------+
| deviceId | deviceName | deviceDescription          | time_stamp          |FuelLevel  | Voltage |
+----------+------------+----------------------------+---------------------+-----------+---------+
|        1 | System 1   | Main System in Server Room | 2010-01-03 00:00:00 |        80 |     219 |
|        2 | System 2   | Outdoor System             | 2010-01-03 00:00:00 |       99  |     220 |
+----------+------------+----------------------------+---------------------+-----------+---------+

You can't do the "limit 1" at the outer level, you loose what you are looking for... ALL devices last entry. Use a pre-query for the last ID of each device, then join back...

select 
        Devices.*,
        DataLog.Time_Stamp,
        DataLog.FuelLevel,
        DataLog.Voltage
    from
        ( select DeviceID,
                 max( ID ) LastActionID
              from
                 DataLog
              group by 
                 1 ) LastInstance
        join DataLog
            on LastInstance.LastActionID = DataLog.ID
        join Devices 
            on LastInstance.DeviceID = Devices.DeviceID
   order by 
       Devices.DeviceName

Per your last comment, I would actually change to something like...

Update your device table with a "LastLogID". Then, via a trigger an insert into your DataLog table, update the Device table immediately with that new ID... This way, you never need to pre-query the data log directly.. You'll already HAVE the last ID and run from that directly to the data log joined by that ID.

I know it's horrible, not elegant and time consuming, but this query works:

SELECT deviceId,deviceName,deviceDescription,
  (SELECT time_stamp FROM datalog
    WHERE datalog.DeviceId=devices.deviceId
    ORDER BY time_stamp DESC LIMIT 0,1) time_stamp,
  (SELECT FuelLevel FROM datalog
    WHERE datalog.DeviceId=devices.deviceId
    ORDER BY time_stamp DESC LIMIT 0,1) FuelLevel,
  (SELECT Voltage FROM datalog
    WHERE datalog.DeviceId=devices.deviceId
    ORDER BY time_stamp DESC LIMIT 0,1) Voltage
FROM devices

I tried to have a single subquery retrieving multiple columns, but MySql complains because it wants only one column.

try

by the way if u want only latest row then u can search it by auto increment field (datalog_table.id)

SELECT dvc.deviceId,dvc.deviceName,dvc.deviceDescription,
       dtl.time_stamp,dtl.FuelLevel,dtl.Voltage 
FROM  device_table dvc
INNER JOIN datalog_table dtl
ON dtl.DeviceId=dvc.deviceId
ORDER BY dtl.id  LIMIT 1

  SELECT
  d.deviceId, d.deviceName, d.deviceDescription, 
  dl.time_stamp, dl.FuelLevel, dl.Voltage 
  FROM Device d, DataLog dl 
  WHERE d.deviceId=dl.deviceID 
  ORDER BY time_stamp DESC
  LIMIT 1