help me with my subquery

[JJ26]

Hello guys,
kindly please help me with my problems that im having it for abt 1 week, and i can;t solve it.
Im using MySQL 4.0.21 and I have 2 tables :

[payment] [cashier]
id id
amtpay stockid
subtot

[payment].id is correlated to [cashier].id

for examples i have 2 records in cashier, and 3 records in payment.
and i want to make a join table, but im failed
the errors goes like this "Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select select id, sum(subtot) as subtot from cashier group by id) as A inner join"

Here's i quot my full syntax:

select A.id , A.subtot, B.amtpay
from (select id, sum(subtot) as subtot from cashier group by id) as A inner join
(select id, sum(amtpay) as amtpay from payment group by id ) as B
 A.id = B.id;

Thanks

 

[cLFlaVA]

From the MySQL Online Manual:

[tt]Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.[/tt]

You'll have to rewrite your subquery as a JOIN.

*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[JJ26]

could you explain to me more specifically??

I don't get your point, sorry.

I need to make a temporary select table in order to get the sum of subtot field (in cashier table ), and get the sum of amtpay (in payment table). Because it'll be giving unexpected result if I firstly "inner join" those 2 tables, and do the aggregate function (SUM).

Here's quotation that I read in the reference that i downloaded from MySQL official website (which is also driving me to write my SQL statement referring to its rule).
[blue]
For illustration, assume that you have this table:

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

Here’s how to use a subquery in the FROM clause, using the example table:

INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;
[/blue]

Thanks

 

[cLFlaVA]

What I meant by that was, since you're using 4.0.21, and subqueries are not supported until 4.1, you'll have to find another way to do your select, most likely with a JOIN.

*cLFlaVA
----------------------------
Breaking the habit...

 

[JJ26]

Thanks alot for your info. I've read in the manual stating the same procedure as you said.
So I guess I have to download the next version of 4.1.X

ciao....