Hello friends i have 3 tables like below :
property_master -->p_id, p_name,r_id
transction_master -->t_id,p_id,amount,user_cat_id,r_id
user_trans_cat-->user_cat_id,cat_name,trans_id ,r_id
my query as below
SELECT property_master.p_id AS "Product Code",
property_master.p_name AS "Description",
(SELECT SUM(amount)
FROM transction_master ,property_master
JOIN user_trans_cat ON transction_master.user_cat_id = user_trans_cat.user_cat_id
WHERE user_trans_cat.trans_id =1 and transction_master.date between '2015-01-01' and '2015-12-31'
group by property_master.p_id
) AS "Income",
(SELECT SUM(amount)
FROM transction_master ,property_master
JOIN user_trans_cat ON transction_master.user_cat_id = user_trans_cat.user_cat_id
WHERE user_trans_cat.trans_id =2 and transction_master.date between '2015-01-01' and '2015-12-31'
group by property_master.p_id
) AS "Expense"
FROM property_master ,transction_master where property_master.r_id = 1
Group by property_master.p_id
Transaction table
Output Came i want total income , expense value as per proeprty wise but when i run above query it will gave me total income value for all property any idea how can i solve it ?
Aucun commentaire:
Enregistrer un commentaire