a query problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bugthefixer
Forum Contributor
Posts: 118
Joined: Mon Mar 22, 2004 2:35 am

a query problem

Post 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 ....
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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?
Last edited by jamiel on Tue Jul 11, 2006 8:02 am, edited 1 time in total.
bugthefixer
Forum Contributor
Posts: 118
Joined: Mon Mar 22, 2004 2:35 am

Post 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 ..
Post Reply