Sorin,
>> Okay, here for another wild guess:
>> SELECT u.login, p.name, sp.name, SUM(t.time)
>> FROM t_user u
>> LEFT JOIN t_project p ON 1
>> LEFT JOIN t_subproject sp ON p.id = sp.project_id
>> LEFT JOIN t_time t ON ?? = ??
>> WHERE u.login = 'amg'
>> GROUP BY p.name
>> Where I'm not sure if you will need the WHERE clause at all.
> This query doesn't work for me.
> It gives back a Sum of time but this is wrong.
> Example:
> +-------+----------------------+-----------------+----------+
> | login | name | name | SUM |
> +-------+----------------------+-----------------+----------+
> | amg | Administration | database minimum| 120 |
> | amg | Allgem. Verbesserung | database upgrade| 0 |
> | amg | Ausbildung | Intern | 2512 |
> | amg | Ausfallkurve IPM | NULL | 0 |
> | amg | Betreuung / Besprech | database minimum| 0 |
> | amg | Bezahlte Absenzen | hvjm | 0 |
> | amg | Bogie Tools | Support | 360 |
> | amg | CCP | CCP | 0 |
This doesn't look like an actual output (should be SUM(t.time)). Could
you provide the actual output mysql gives you?
> Mistake 1: There are no time entries for subproject database minimum at all.
Yes, but you have entries for "Administration", summing up to 120
minutes. To sum up the times for "database mininum" etc., you would
have to GROUP BY sp.name.
> Mistake 2: There are time entries for the subproject 'Intern' but thy
> dont belong to 'amg'
This seems to be rather a database design case than a query case.
You're thinking of "Administration" as of a project, and of "database
minimum" as a subproject of "Administration". That is not true, as you
can see in the table above. "database minimum" can be a "subproject"
of "Betreuung/Besprechung" as well. So, most probably, there's a
logical break in your database design.
I guess the table design should be as follows:
TABLE user: Has "amg" etc.
TABLE task: Has "Administration" etc.
TABLE project: Has "database minimum" etc.
Those are your base tables ("entities"). Furthermore, you need a
lookup table like this:
TABLE user_task_project with columns: user_id, task_id, project_id
(the three together form your PRIMARY KEY for that table)
Now, whenever a user does anything (like "Administration" of "database
minimum"), insert an entry into that lookup table like this:
INSERT INTO user_task_project VALUES (10,121,42)
Where 10 would be user_id of "amg", 121 would be the task_id of
"Administration", and 42 would be the project_id of "database
minimum".
Or, in other words: As long as you don't have new users, new tasks, or
new projects, you don't touch those base tables at all. For all work
that is done, you only need to insert values into the lookup table.
Hope that helps!
Regards,
--
Stefan Hinz <[EMAIL PROTECTED]>
iConnect GmbH <http://iConnect.de>
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
[filter fodder: sql, mysql, 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
