MySQL joins two tables with maximum value in another field

Question

I have two tables and a balance

/---------------------\ | cid | name | mobile | |---------------------| | 1 | ABC | 12345 | |---------------------| | 2 | XYZ | 98475 | \---------------------/ /----------------------------\ | date | cid | balance | |----------------------------| | 2013-09-19 | 1 | 5000 | |----------------------------| | 2013-09-19 | 2 | 7000 | |----------------------------| | 2013-09-20 | 1 | 300 | |----------------------------| | 2013-09-20 | 2 | 4500 | |----------------------------| | 2013-09-21 | 2 | 600 | \----------------------------/ 

I would like to join this table and get the balance of the maximum date for a particular cid.

Output result as -

 /--------------------------------------------\ | cid | name | mobile | date | balance | |--------------------------------------------| | 1 | ABC | 12345 | 2013-09-20 | 300 | |--------------------------------------------| | 2 | XYZ | 98475 | 2013-09-21 | 600 | \--------------------------------------------/ 

Answer 1

You need to use two subqueries, for example:

 SELECT a.cid, a.name, a.mobile, b.date, b.balance FROM account a JOIN ( SELECT b1.* FROM balance b1 JOIN ( SELECT cid, MAX(Date) As maxDate FROM balance GROUP BY cid ) b2 ON b1.cid = b2.cid AND b1.date = b2.maxDate ) b ON a.cid = b.cid; 

Output:

 | CID | NAME | MOBILE | DATE | BALANCE | |-----|------|--------|----------------------------------|---------| | 1 | ABC | 12345 | September, 20 2013 00:00:00+0000 | 300 | | 2 | XYZ | 98475 | September, 21 2013 00:00:00+0000 | 600 | 

See SQLFiddle

Answer 2

 SELECT a.cid, a.name, a.mobile, MAX(b.date), b.balance FROM account AS a INNER JOIN balance AS b WHERE a.cid=b.cid GROUP BY cid; 

Sorry, I do not see the balance column in the third table.

 SELECT a.cid, a.name, a.mobile, b.date, b.balance FROM account AS a INNER JOIN ( SELECT c.date, c.cid, c.balance FROM balance AS c INNER JOIN ( SELECT cid AS cid2, MAX(date) AS date2 FROM balance GROUP BY cid2) AS d ON c.cid=d.cid2 AND c.date=d.date2 ) AS b ON a.cid=b.cid GROUP BY cid;-- 

Answer 3

This next statement should get the desired result:

 SELECT cid, name, mobile, MAX(date), blance FROM account LEFT JOIN balance ON account.cid = balance.cid GROUP BY balance.cid