CODEWITHSUNDEEP
sqlms-access
Berry
Berry

Reputation: 128

SQL creating a log

I'm trying to create a query that gives me a log of hours spend per person per task.

I have the following tables:

Eventually the log should look something like this:

| Person1 | Task1 | x hours |
| Person1 | Task2 | x hours |
| Person2 | Task1 | x hours |

So it has to be grouped by both person and task.

It's the total hours that's giving me trouble.

So far this is what I have:

SELECT m.Name, t.Task, Sum(l.Hours) AS TotalHours
FROM Member AS m, Log AS l, Tasks AS t
WHERE t.TaskID=l.TaskID
GROUP BY t.Task, m.Name;

However, this gives me a list of unique person-task combinations but with the total amount of hours spend on that task and not the total amount of hours spend on that task per person.

Hopefully I've explained the problem clear enough so that you can help me.

Many thanks in advance.

Upvotes: 2

Views: 97

Answers (2)

DMK
DMK

Reputation: 2527

You could include your Member table within the where clause.

So If your Log table also includes MemberId your Sql Query would read :-

Select m.Name, t.Task, Sum(l.Hours) As TotalHours
From Member AS m, Log AS l, Tasks AS t
WHERE t.TaskID=l.TaskID and m.MemberId=l.MemberId
GROUP BY t.Task, m.Name

This should give you the results you require.

Edit :

If you did want to use explicit rather than implict joins then the Query would read something like the following :-

Select m.Name, t.Task, Sum(l.Hours) As TotalHours
From Log AS l
Inner join tasks AS t
On t.TaskID=l.TaskID
Inner join Member AS m
On m.MemberId=l.MemberId
GROUP BY t.Task, m.Name

I generally use explicit joins as my queries are considerably larger and I find explicit joins easier to read. As for efficiency and other factors, I am unsure.

Upvotes: 3

Joe Meyer
Joe Meyer

Reputation: 4405

You need to join back to your Member table also. In your posted example you are only joining the Log and Tasks Table.

If you provided some more information about what is in the member's table i'd be happy to assist.

Upvotes: 1

Related Questions