Page 1 of 1

a query problem

Posted: Tue Jul 11, 2006 7:31 am
by bugthefixer
I hav a database structure something like this

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_code

this 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 ....

Posted: Tue Jul 11, 2006 7:47 am
by jamiel
It's not clear how your Amount is meant to increment.

Also, are there some ams_head_code 's that are in some tables and not the others?

Posted: Tue Jul 11, 2006 7:57 am
by bugthefixer
thanx for quick reply ... now i have modified my query and the result is a bit close but still it does not giv the desired result

Code: Select all

select sum(a.ams_budget_amount), c.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 AND d.ams_head_code = c.ams_head_code AND d.ams_sub_head_code = e.ams_sub_head_code) OR 
		(a.ams_head_code = e.ams_sub_sub_head_code AND d.ams_head_code = c.ams_head_code AND d.ams_sub_head_code = e.ams_sub_head_code)
		GROUP BY c.ams_head_code

Now problem with query is it requires account head to have both sub head and also sub sub head ...... if this condition is justified then it gives the correct result....

If there is some possibility so that i can convert this query into LEFT OUTER join type of query then this problem can be solved ..