table 1 : ams_budget_allocation(ams_head_code, ams_budget_amount)
table 2 : ams_account_head(ams_head_code)
table 3 : ams_account_sub_head(ams_sub_head_code, ams_head_code)
table 4 : ams_account_sub_sub_head(ams_sub_sub_head_code, ams_sub_head_code)
Now the ams_head_code in table 1 can contain a value that corresponds to any of the three codes in rest of the three tables that are interlinked.
I am trying to execute this query so that i can get the sum of amount for a particular account_head
Code: Select all
select sum(a.ams_budget_amount), a.ams_head_code
from ams_budget_allocation a, ams_account_head c, ams_account_sub_head d, ams_account_sub_sub_head e
WHERE a.ams_head_code = c.ams_head_code OR
a.ams_head_code = d.ams_sub_head_code OR a.ams_head_code = e.ams_sub_sub_head_code
GROUP BY a.ams_head_codethis query returns much more amount for a particular head than what is actually assigned to each head.
Anybody can help me with this problem. I know this query is missing something ....