Hi everybody,
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),deducti on1(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),deductio n1(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

client table stores data of distinct clients.

sale table stores data of various clients, amount, date.

purchase table stores data of various clients, amount, date.

daily table stores data of various clients, ampunt paid, date.

Now I want an outstanding report for each client, for sale and purchase independantly.
Like in these column structure:
CLIENT TOTALSALES PAID DUE
A 100 50 50
B 450 100 350
I am running this euery:
#########################CODE##################### ##
SELECT DISTINCT sale.client, SUM(sale.amount), SUM(daily.amount) FROM
purchase, daily WHERE sale.client = daily.client
########################CODE###################### ##
BUt ende up with wrong results. pls help with proper query

you could not possibly have run that query, because it's missing a GROUP BY clause

what is the purpose of the "daily" table?

daily table stores data of various clients, ampunt paid, date.
I have tied the query with GROUP BY purchase.client, it is returning a result with amount of sales and amount of payment by each client multiplied by the number of times a particular client is present in daily and purchase table repectively.
Any solution?
thanks and regards

isn't that what the payments table is for?

the problems you are describing are due to trying to sum up two unrelated one-to-many relationships at the same time

can you write separate queries for each of the tables, to get one row per customer? if you show me those, i will show you how to combine them

This is the query to collect sum of sales amounts for each client from sales table.

###################################code########### ###########################################
SELECT DISTINCT sales.client, SUM(sales.amount) FROM sales GROUP BY sales.client
################################################## ##############################################

This is the query for collecting sum of paid amounts form daily(for payment)table by each client.

#############################################code# ##########################################
SELECT DISTINCT daily.client, SUM(daily.amount) FROM sales WHERE sales.client = S GROUP BY sales.client
################################################## ##########################################

Now how can I combine them?

thanks and regards.

the first of your two queries does not require the DISTINCT --the second does not work by itself -- did you test it?