vendredi 10 avril 2015

Getting Duplicate total value in multiple join of table SQLITE

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


enter image description here


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