MySQL Forums
Forum List  »  General

Please a mysql's syntax
Posted by: gary tang
Date: March 27, 2005 08:06PM

My mysql version:4.0.24
I have create three tables

source table:
id form number
-------------------------
A a1 2
A a2 3
B b1 4
C c1 5

sell table :
id form number date
------------------------------------------
A a1 1 2005-03-20
A a1 2 2005-03-21
A a2 3 2005-03-23
B b1 3 2005-03-23


input table :
id form number date
------------------------------------------
A a1 4 2005-03-25
A a2 5 2005-03-25
C c1 2 2005-03-26
C c1 3 2005-03-27

Now I hope to collect three table like below:

id form source.number sell.number input.number
-----------------------------------------------------------------------
A a1 2 3 4
A a2 3 3 5
B b1 4 3 null
C c1 5 null 5

My way is create temporary table to finish it.
My way:
mysql>create temporary table test type=heap select source.id,source.form,source.number,sum(sell.number) as sellnumber from source left join sell on source.id=sell.id and source.form=sell.form group by source.id,source.form;

mysql>create temporary table test1 type=heap select test.id,test.form,test.number,test.sellnumber,sum(inputl.number) as inputnumber from test left join input on test.id=input.id and test.form=input.form group by test.id,test.form;

Now,
My question is how can I use only one sentence to get the same result??

Options: ReplyQuote


Subject
Written By
Posted
Please a mysql's syntax
March 27, 2005 08:06PM


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.