Hi, Try the following: SELECT p.name, SUM( t.minutes ) FROM t_project p LEFT JOIN t_subproject sp ON (p.id = sp.project_id) LEFT JOIN t_time t ON (sp.id = t.subproject_id) GROUP BY p.id;
(i prefer grouping by id than by name). I didn't understand why your query didn't result, don't know the data you have inserted. I tested the above query and i think it gives the results you want. The big difference is that i used LEFT JOIN instead of INNER JOIN because i assumed that you may have projects which have or may have not subprojects, (and you may have defined subprojects which doesn't already have the minutes defined). Hope this helps. On Thu, 2003-02-27 at 10:32, Sorin Marti wrote: > Hi all, > > I've got following question: > I've got three tables: > > TABLE t_project (id, name,dossier_id, PRIMARY KEY(id)) > TABLE t_subproject (id, name,project_id, PRIMARY KEY(id)) > TABLE t_time (id, minutes, subproject_id,PRIMARY KEY(id)) > > Now I want to dp a SELECT which has following effect: > > Projectname =A6 minutes > testname =A6 300 > > In this example testname should be a value of the column name from the > table t_project. '300' should be the SUM of all 'minutes' (from t_time) > which have the the 'subproject_id' of subprojects which have the > 'project_id' of the project'name': 'testname' > > I tried following query: > > SELECT p.name, SUM( z.minutes ) FROM ( ( t_zeit z INNER JOIN > t_subproject sp ON sp.id =3D z.subproject_id ) INNER JOIN t_project p ON= > sp.project_id =3D p.id ) GROUP BY p.name > > But it has not the efeect I want... > What is wrong? > > I hope you understood my problem (sorry for the bad English) and can > send me your suggestions.... > > Thanks in advance > > Sorin Marti -- Diana Soares --------------------------------------------------------------------- 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
