CODEWITHSUNDEEP
mysqlsortingdatetimeinner-joincolumnsorting
Zeb
Zeb

Reputation: 13

mysql query of 2 tables with inner join

I want to join Table A and Table B and display the most recent entry for each truck.

  1. Table A columns: TA ID,Truck_Num,Serial_Num
  2. Table B columns: TB ID,Serial_Num,Lat,Long,DateTime

Desired output 

 Truck   |     DateTime     |    Serial_Num   |       Lat   |      Long                
   1     |     datestamp    |       123       |        1    |       -1                                     
   2     |     datestamp    |       456       |        2    |       -2

using join query

SELECT a.Truck b.Lat b.Long b.DateTime FROM TB as b INNER JOIN TA AS a a.Serial_Num=b.Serial_Num;

I'v tried this but it is only giving me the most recent entry's time.

SELECT a.Truck b.Lat b.Long b.DateTime FROM TB as b INNER JOIN TA AS a a.Serial_Num=b.Serial_Num WHERE b.DateTime = (SELECT MAX(b.DateTime) FROM TB tb WHERE a.Truck_Num=Truck_Num);

Upvotes: 1

Views: 122

Answers (2)

Liannis
Liannis

Reputation: 111

This will show the most recent item per Truck. If the rest of the information that you want to show is the same in all rows then you can put it on the group by clause, if not then you have to do a join with this result

SELECT Serial_Num, MAX(DateTime) FROM TB GROUP BY Serial_Num;

This is the join you have to do if the data is not equal and you cannot put it on the group by clause

SELECT Truck_Num, Lat, Long, maxDateTime FROM TB as b 
   INNER JOIN TA AS a ON a.Serial_Num=b.Serial_Num
   INNER JOIN (SELECT Serial_Num, MAX(DateTime) as maxDateTime
               FROM TB GROUP BY Serial_Num) as c 
               ON b.Serial_Num=c.Serial_Num AND maxDateTime = DateTime

Upvotes: 2

Dinh Phong
Dinh Phong

Reputation: 596

It's should work, you can check this question: Select only one row with max field with One-Many Relationship Table

SELECT
    ta.Truck,
    tb.Lat,
    tb.Long,
    MAX(tb.DateTime),
    ta.Serial_Num as Serial_Num
FROM
    TB as tb
INNER JOIN
    TA as ta
ON (tb.Serial_Num = ta.Serial_Num)
GROUP BY
    tb.Serial_Num

Upvotes: 0

Related Questions