I've seen this kind of question asked, but have not found any useful answers. So, at the risk of being redundant and repeating what has already been covered:
I want to join the groupings of table columns. The tables are potentially different lengths. A toy example (I figure bankers/accountants have been using SQL longer than anyone :-) ): mysql> select * from deposits; +------------+------+---------+ | date | acct | damount | +------------+------+---------+ | 2002-09-21 | 1 | 100.00 | | 2002-09-21 | 2 | 20.00 | | 2002-09-21 | 1 | 75.00 | +------------+------+---------+ 3 rows in set (4.73 sec) mysql> select * from withdrawals; +------------+------+---------+ | date | acct | wamount | +------------+------+---------+ | 2002-09-21 | 2 | 5.00 | | 2002-09-21 | 1 | 50.00 | +------------+------+---------+ 2 rows in set (0.45 sec) Ideally, I would like to end up with: +------------+------+---------+---------+ | date | acct | damount | wamount | +------------+------+---------+---------+ | 2002-09-21 | 1 | 175.00 | 50.00 | | 2002-09-21 | 2 | 20.00 | 5.00 | +------------+------+---------+---------+ Of course, doing: mysql> select w.date,w.acct,sum(damount),sum(wamount) from withdrawals as w left outer join deposits as d on (w.acct = d.acct) group by w.date,w.acct; +------------+------+--------------+--------------+ | date | acct | sum(damount) | sum(wamount) | +------------+------+--------------+--------------+ | 2002-09-21 | 1 | 175.00 | 100.00 | | 2002-09-21 | 2 | 20.00 | 5.00 | +------------+------+--------------+--------------+ 2 rows in set (2.49 sec) is wrong. Doing multiple queries and/or creating a permanent or temporary transaction table works, but I was hoping to get something in one select, without having to transform tables being created from separate sources. I almost thought this would work (mySQL >4.0): mysql> select date,acct,sum(damount) from deposits group by date,acct union select date,acct,sum(wamount) from withdrawals group by date,acct; +------------+------+--------------+ | date | acct | sum(damount) | +------------+------+--------------+ | 2002-09-21 | 1 | 175.00 | | 2002-09-21 | 2 | 20.00 | | 2002-09-21 | 1 | 50.00 | | 2002-09-21 | 2 | 5.00 | +------------+------+--------------+ 4 rows in set (0.73 sec) Not quite. Any thoughts? I would think this is a well-known and well-solved problem. My SQL skills are slap-dash at best, learned in fits and starts, so forgive me if I've missed something obvious here. Thanks! Carl --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
