mysql query returning results multiplied

[ritwik]

I am usng MYSQL version 4.1 and PHP 5.0 on windows XP.

I had some date related to sales, purchase and payment.

Now I want to convert that in PHP-MYSQL based software application.

I have four tables in a database. Tables and there structures are:

 

TABLE sale: client(varchar,100),item(varchar,10),date(datetime),rate(dcimal,14,2),quantity(decimal,14,2),deduction1(decimal,14,2),deduction2(decimal,14,2),addtion(decimal,14,2)

 

TABLE purchase: client(varchar,100),item(varchar,10),date(datetime)rate(dcimal,14,2),quantity(decimal,14,2),deduction1(decimal,14,2),deduction2(decimal,14,2),addtion(decimal,14,2)

 

TABLE payment: client(varchar,100),item(varchar,10),date(datetime),amount(decimal,14,2),purpose(varchar,100)

 

TABLE client: client(varchar,100)parimary key

 

Data enetered into the sales,purchaseand payment table ae with respect to client table,i.e. name of teh client stored in this table.

 

Sale amount of an item for a client is calculated as rate*quantity+addtion-deduction1-deduction2 and so is purchse calculated.

 

payment table stores data of payments of various clients paid for the sales on a diferrent date and payments made to the cleints for purchase on a diferrent date.

 

Now I want to generate a report of due statement, in this format--

client totalamount paid balance

 

and also same for the purchase(payemnts to be made).

 

What shall be my mysql queries and how can i display data feteched and calculated to the required format(in HTML tables).