Get latest date from table with their line in mysql

Question

I have 2 tables: Asset and Inventory.

Table Asset

Table Inventory

When I run the following mysql query:

SELECT a.assetnum,a.description,a.user,MAX(b.invdate) as invdate , b.note 
FROM asset a 
LEFT JOIN inventory b on a.assetnum=b.assetnum 
GROUP BY a.assetnum, a.description

I get is this:

but, it should be like this....

Whats wrong with my mysql query? Can somebody help me...

Answer 1

You basicaly get a random note in the result. You probably need something like this:

 select a.assetnum,a.description,a.model, i.invdate, i.note 
 from inventory i
 join
 (
   select assetnum, max(invdate) max_invdate
   from inventory
   group by assetnum
 ) t on i.assetnum = t.assetnum and
        i.invdate = t.max_invdate
 right join asset a on a.assetnum = i.assetnum