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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
