MySQL Forums
Forum List  »  Newbie

QUERY not working use of subselects is too
Posted by: Firdos Ali
Date: July 12, 2005 11:33AM

Let me give you a simple example:

TABLE A {
ID INT,
NAME VARCHAR(255),
}

TABLE B {
ID INT
AMOUNT DOUBLE,
AREF INT //foreign key to table a
}

TABLE C {
ID INT
AMOUNT DOUBLE,
AREF INT //foreign key to table b
}

Data in Table A:
ID - Name
1 - "First Booking"

Data in Table B:
ID - Name - Aref
1 - 25.00 - 1
2 - 30.00 - 1

Data in Table C:
ID - Name- Aref
1 - 10.00 - 1
1 - 20.00 - 1
1 - 30.00 - 1

Now I want to retreive the following information: Give me the sum(Amount from B), sum(Amount from c) and group the results by A

The query should be as follows:
select sum(b.Amount), sum(c.Amount) from B b, C c where b.Aref = c.Aref and b.Aref = 1;

I would hope the result would be 55.00 and 60.00 respectively. However the result that is returned is 165.00 and 120.00 respectively. I found out that the data is multiplied sum(b.Amount) * data result in C and sum(c.Amount) * data result in B.

After much work I found that this query will work:
select sq1.bsum, sq2.csum
From
(select sum(b.Amount) bsum, a.Id aid from B b, A a where b.Aref = a.Id group by a.Id
)sq1
Inner Join
(SELECT sum(c.Amount) csum, a.Id aid from C c, A a where c.Aref = a.Id group by a.Id ) sq2
on sq1.aid=sq2.aid

I would like a much simplier query than the one above. I am using Hibernate and with the above query I run into more problems, so I need a query that is much simplier. The root of my problem is that if I query the sums seperately, that is two queries, than everything works fine. The problem is that there is a lot of data in our database and doing two queries is not feasible, so we need a single query to make this work.

Options: ReplyQuote


Subject
Written By
Posted
QUERY not working use of subselects is too
July 12, 2005 11:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.