How to fetch record from two table in one query

Question

I have two table

1. rental_detail -> [ id, Building, Floor, Flate_no, Name,doj]

rental_detail Stores the information of the person.
this is rental_detail

|id|name|building|Floor|Flate_no|doj |1 |aana| C | 1st |c-101 |06-12-2015

And in

2. Monthly_rent -> [ id, Building, Floor, Flate_no, date ] Stores the details of per month rent pay by the person

this is month_rent

|id|building|Floor|Flate_no|date |rent| |1 | C | 1st |c-101 |02-01-2016|2000 |2 | C | 1st |c-101 |03-02-2016|2000 |1 | C | 1st |c-101 |06-12-2015|2000

I need the Max date from Monthly_rent and all information about person from rental_detail where building is c, floor is 1st and flate is c-101

I tried this query:

select *,max(mr.date) as m 
from monthly_rent mr 
Inner join rental_details rd 
   on rd.building='C' and rd.floor = '1st' And rd.flate_no='C-101

I need this

|1|aana|C|1st|c-101|03-02-2016|

aana pay last rent at 03-02-2016

Answer 1

Try this:

SELECT rd .*,mr.date AS mr_date  
FROM rental_details rd 
LEFT JOIN monthly_rent mr 
ON rd.date = (SELECT MAX(date) FROM monthly_rent WHERE building='C' )
WHERE rd.building='C' AND rd.floor = '1st' AND rd.flate_no='C-101'