Hello frirnds i have 5 table as below
Property_master -->p_id, p_name, p_address,p_city,P_state,p_country,p_is_active
Tanant_master-->t_id,p_id,t_name,
Rent--> r_id,p_id,r_amount,r_date,
Expense-->e_id,p_id,e_amount, e_date
Mileage-->m_id,p_id,m_total,m_date
and my query is as below
SELECT property_master.p_id AS "PID",
property_master.p_name AS "PropertyName",
property_master.p_address AS "ProepertyAddress",
property_master.p_city AS "ProepertyCity",
property_master.p_state AS "ProepertyState",
property_master.p_country AS "ProepertyCountry",
property_master.p_isrent AS "ProepertyStatus",
property_master. p_rent AS "PropertyRent",
tanant_master.t_name AS "TanantName",
tanant_master.t_start_date AS "TanantStartDate",
tanant_master.t_end_date AS "TanantEndDate",
(SELECT SUM(rent_amount)
FROM rent_master
WHERE p_id = property_master.p_id and rent_master.rent_date between '2012-01-01' and '2012-12-31'
) AS "TotalIncome",
(SELECT SUM(e_amount)
FROM expense_master
WHERE p_id = property_master.p_id and expense_master.e_date between '2012-01-01' and '2012-12-31'
) AS "TotalExpense" ,
(SELECT SUM(m_total)
FROM milage_tracker_master
WHERE p_id = property_master.p_id and milage_tracker_master.m_date between '2012-01-01' and '2012-12-31'
) AS "TotalMileage"
FROM property_master ,tanant_master
where property_master.p_is_active=1 and property_master.p_id=tanant_master.p_id
GROUP BY property_master.p_id,tanant_master.t_id
when i run above query it will give data only that property which has tanant assign to that property but i want that property also which has tanant not assign so any idea how can i solve it?
Aucun commentaire:
Enregistrer un commentaire