I have two tables one is retailer table and other is visit master table. In retailer table all the shops are stored and in visit master those shops are mentioned in which user has visited. So I want to fetch those retailers shop in which he has not visited for last ten days whether shops are from both tables in visit master those shops in which he has visited and there are some shops in which he has not visited till now which we get from retailer table.
created date column is from retailer table and visit_datetime column is from visit master table.
SELECT tbl_retailer.sr_id,
tbl_retailer.retailer_shop_name,
tbl_retailer.retailer_id,
tbl_retailer.tsm_id,
tbl_retailer.created_date,
max(tbl_visit_master.visit_DateTime) as last_visit_date,
max(tbl_feedback.visit_date) as feedback_given_date,
max(tbl_stock.taken_date) as stock_date,
max(tbl_promise_order.promise_date) as order_date,
max(tbl_payment_collection.payment_date) as payment_date
from tbl_retailer
left join tbl_member on tbl_member.employee_id=tbl_retailer.sr_id
left join tbl_feedback on tbl_retailer.retailer_id=tbl_feedback.retailer_id
left join tbl_stock on tbl_retailer.retailer_id=tbl_stock.retailer_id
left join tbl_promise_order on tbl_retailer.retailer_id=tbl_promise_order.retailer_id
left join tbl_payment_collection on tbl_retailer.retailer_id=tbl_payment_collection.retailer_id
left join tbl_visit_master on tbl_retailer.retailer_id=tbl_visit_master.retailer_id
where tbl_retailer.sr_id=189
group by retailer_id
having max(tbl_visit_master.visit_DateTime) and tbl_retailer.created_date <= CURDATE() - INTERVAL 10 DAY;
See Question&Answers more detail:os