Look at the HAVING clause. c are columns, t are tables
SELECT c1, c2, c3, sum(c4) FROM <tables> WHERE t1.c1 = t2.c1 AND t2.c2 = t3.c2 HAVING sum(c4) > 0 -ms -----Original Message----- From: Sorin Marti [mailto:[EMAIL PROTECTED] Sent: Friday, March 21, 2003 2:36 AM To: [EMAIL PROTECTED] Subject: Re: SELECT over more than one table Hi all, I tried around a long time. Has no one an idea? my query: --------- SELECT u.login, p.name, sp.name, t.minutes FROM t_user u LEFT JOIN t_project p ON 1 LEFT JOIN t_subproject sp ON p.id = sp.project_id that gives me every subproject and the project where it belongs to for every user. Now I want the SUM of the time (t.hours) worked on the subprojects (for every user) but I don't want the time if the SUM is 0. Thanks for EVERY advice Sorin > > Hi all, > > I've got a problem and I really can't solve it by myself :-( > > I want a Select-Statement which gives me following output: > > Projectname / Subprojectname / user / hours > ------------------------------------------- > testproject / subproject1 / mas / 1 > testproject / subproject1 / hud / 2 > testproject / subproject1 / keh / 5 > > testproject / subproject2 / mas / 1 > testproject / subproject2 / hud / 0 > testproject / subproject2 / keh / 3 > > testproject / subproject3 / mas / 1 > testproject / subproject3 / hud / 3 > testproject / subproject3 / keh / 5 > ------------------------------------------- > > So I want to know which user worked how long on the subprojects of a > certain project. > My database looks like that: > > t_time: id, hours, subproject_id, user_id > t_user: id, name > t_subproject: id, name,project_id > t_project: id, name > > a subproject can have X times and a project X subprojects. > subproject_id in t_time is linked with id in t_subproject, project_id in > t_subproject is linked with id in t_project. > > > How can I do that? I did some left joins but didn't get the right result. > > Thanks > Sorin sql, query --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
