On Tue, 8 Feb 2005 11:37:20 +0000, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > On Tuesday 08 February 2005 08:09, Joachim Klöfers wrote: > > Hi, all > > > > I hope somebody can help me. > <bigsnip /> > > > > > Many thanks in advance > > Joachim > > That is a very thorny problem. May I observe that you will find things much > easier, if you add unique primary keys to tables 2 & 3, thus: > > ALTER TABLE <whatever_the_table_is_called> ADD pri_key MEDIUMINT > AUTO_INCREMENT PRIMARY KEY FIRST; > > You can then left join the second table to itself and get along on the first > stage of solving the wider problem. Assuming the second table is called > table2, for instance, you could do something like.... > > SELECT t2a.id,SUM(IF(t2a.current='J',t2a.amount1,0)) as 'amount1' FROM table2 > AS t2a LEFT JOIN table2 as t2b ON t2a.pri_key!=t2b.pri_key AND t2a.id=t2b.id > AND t2a.current=t2b.current GROUP BY a.id; > (Note, this only solves the 'sum if current' part of the problem, but this is > certainly one of the harder aspects that needs solving, anyway) > > Even that is probably far more tortuous than it needs to be, but I can't think > of anything better, at present. Anyway, as I say, I personally don't see how > you will solve the wider problem without some means of distinguishing unique > records from one another in tables 2 & 3. > > I'd also ask you to consider whether it was even wise to attempt to solve this > type of thing with a single query, if it means that anyone trying to modify > it at a future date is going to have to gaze at it for several hours before > they could begin work on the alterations :). > > Hope that helps to some extent. > > Dan > Hmm..
What's wrong (for the first part) in simply doing : SELECT region, SUM(amount1) FROM t1 , t2 WHERE t1.id=t2.id AND current='J' GROUP BY region works for me. Now to see about the sum of amount2.... -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
