select a.user_id,a.room_id,b.room_type,datediff(date(checkout_time),date(checkin_time)) days
from checkin_tb a
left join guestroom_tb b
on a.room_id=b.room_id
where datediff(checkout_time,checkin_time)>1
order by days asc,room_id asc, user_id desc