Hello friends i have 6 tables ,
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,
property_unit--> unit_id,p_id,unit_name
unit_info-->unit_info_id,unit_id,p_id,p_rent, p_isrent
property_assign-->t_assign_id,unit_info_id,p_id,t_id
transction_master-->t_master_id,p_id,unit_info_id ,t_id ,user_cat_id,amount,date
My Requirement
I need Total income value as per proeprty as well as Property unit wise suppose i have prperty A and in this there are twi units A1, A2 than i need totalincome value for A , A1,A2 and also tenant information which i occupied on it so my query is as below
Query
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_unit.unit_name AS "UnitName",
tanant_master.t_name AS "TName",
property_assign.t_start_date AS "TSDAte",
property_assign.t_end_date AS "TEDate",
(SELECT SUM(transction_master.amount)
FROM transction_master
left join user_trans_cat on user_trans_cat.user_cat_id = transction_master.user_cat_id
JOIN unit_info ON unit_info.unit_info_id = transction_master .unit_info_id
WHERE user_trans_cat.trans_id = 1
AND transction_master.date between '2015-01-01' AND '2015-12-31'
) AS "TotalIncome"
From property_master
LEFT JOIN property_unit on property_unit.p_id=property_master.p_id
left join property_assign on property_master.p_id=property_assign.p_id
left join tanant_master on tanant_master.t_id=property_assign.t_id
where property_master.p_is_active=0 and property_master.r_id=75
GROUP BY property_unit .unit_id , property_master.p_id ,tanant_master.t_id
OUTPUT
when i run above query i get duplicate value of total income in every property any idea how can i solve it ?
Aucun commentaire:
Enregistrer un commentaire