user2997418 user2997418 - 21 days ago 5
MySQL Question

MySQL display rows from 2 tables with most recent data in table 2

I have 2 tables to use, I want display the result depending on the recent data in the table2 where the owner in Table1 = 'ownerA':
Table1 :

# id, name, owner
________________________________________________
19782, device1, ownerA
19783, device8, ownerB
19784, device2, ownerA
19785, device3, ownerA


Table2 :

# nasid, sim, timestamp
_______________________________________
19782, 0, 2015-12-08 15:34:27
19782, 0, 2015-12-08 15:34:33
19772, 0, 2015-12-08 15:34:39
19752, 0, 2015-12-08 15:34:45
19783, 0, 2015-12-08 15:34:50
19712, 0, 2015-12-08 15:34:56
19783, 0, 2015-12-08 15:35:02
19782, 0, 2015-12-08 15:35:07
19784, 0, 2015-12-08 15:35:13
19784, 0, 2015-12-08 15:35:20


What I want to get in output :

# name, nasid, sim, timestamp
_______________________________________
device8, 19783, 0, 2015-12-08 15:35:02
device1, 19782, 0, 2015-12-08 15:35:07
device2, 19784, 0, 2015-12-08 15:35:20


This is what I tried, :

select nasid, sim, MAX(timestamp)
from Table1 T1,Table2 T2
where
Table1.owner='ownerA'
and
T2.timestamp = (SELECT MAX(T2_2.timestamp)
FROM Table2 T2_2
WHERE T2.sim=T2_2.sim)


This is my SHOw CREATE :
Table1:

CREATE TABLE `Table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) COLLATE latin1_general_ci DEFAULT NULL,
`owner` varchar(128) COLLATE latin1_general_ci DEFAULT '',
UNIQUE KEY `name` (`name`),
KEY `id` (`id`),
KEY `owner` (`owner`),
) ENGINE=InnoDB AUTO_INCREMENT=26696 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci


Table2:

CREATE TABLE `Table2` (
`nasid` int(11) DEFAULT NULL,
`sim` char(20) COLLATE latin1_general_ci DEFAULT NULL,
`timestamp` datetime DEFAULT NULL,
KEY `nasid` (`nasid`),
KEY `timestamp` (`timestamp`),
KEY `sim` (`sim`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Answer

Generally I generate a data set with the max values and the unique key then simply join it back to the base set containing the other data we need.

SELECT T1.name, T2.nasid, T2.sim, T2.timestamp
FROM Table1 T1
INNER JOIN table2 T2
 on T1.ID = T2.nasid
INNER JOIN (SELECT max(timestamp) mt, nasid 
            FROM table2 
            GROUP BY  nasid) A
   on A.MT = T2.TimeStamp
  and A.Nasid = T2.Nasid
WHERE T1.Owner = 'ownerA'

Well lets go back to your original query...

I think all you had wrong there was sim should be nasid. as sim doens't appear to be the key to the table. Only nasid and timestamp based on data presented would generate a unique key. on and you're missing the join between t1 and t2.

select name, nasid, sim, timestamp
from Table1 T1,Table2 T2
where Table1.owner='ownerA' 
and T1.Id = t2.nasid
and T2.timestamp = (SELECT MAX(T2_2.timestamp)
                    FROM Table2 T2_2
                    WHERE T2.nasid=T2_2.nasid)