Roman Neuhauser wrote:
# [EMAIL PROTECTED] / 2003-06-04 09:17:01 -0400:
I need all data in sales1 and sales2, but only for records from acc that are in either/both sales1, sales2.
SELECT acc.name, acc.phone, acc.acctno, sales1.amt AS mo1sales, sales2.amt AS mo2sales, (sales1.amt - sales2.amt) AS diff FROM acc LEFT JOIN sales1 USING acctno LEFT JOIN sales2 USING acctno ORDER BY diff
To make sure that the rows exist in either sales1 or sales2, include a WHERE (sales1.acctno IS NOT NULL OR sales2.acctno IS NOT NULL).
Also, I suspect that sales1 and sales2 should really be a single 'sales' table with an extra column indicating which month the sales are for (and maybe one for year as well), but I don't have enough information to be sure.
Bruce Feist
Table sales1 & sales2 are tables generated from a larger table containing way too much information. Tables Sales1 contains only sales tickets summarized by day for month 1, and Sales2 contains same info for month 2. I needed to generate a sales comparison report for 1 store with two different months. Since I did not know how to retrieve in 1 select, I generated three, first pull sales info for month1 to sales1, then pull sales info for month 2 to sales2, then try and combine these to show customers sales and difference between the two months.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Patrick J. Shoaf, Systems Engineer <mailto:[EMAIL PROTECTED]>[EMAIL PROTECTED]
Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or 888-638-6963 Fax: 724-489-4386
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]