Jamon writes "I have a table (EMPLOYEE) with a primary key ID and a varchar field for the employee NAME. The Employee ID is a "one to many" relationship to a second table (TIMECLOCK) that has the employee's ID and an integer field containing the number of HOURS worked in a day and the DATE worked. This second table contains a record for each day that the employee worked:EMPLOYEE Table-----------------| ID | NAME |-----------------| 1 | Joe |-----------------| 2 | Bob |-----------------
TIMECLOCK Table--------------------------| ID |HOURS | DATE |--------------------------| 1 | 2 | 1/1/06 |--------------------------| 2 | 8 | 1/1/06 |--------------------------| 1 | 4 | 1/2/06 | --------------------------| 2 | 6 | 1/3/06 |--------------------------
I need a a single select statement that returns a dataset with the employee's total hours worked and the employee's name. I can do the first part of the query but do not know how to combine it with the employees name. I want to use the ID from this query: select sum(HOURS)as TOTAL_HRS, ID from TIMECLOCK group by ID
to get the employee's name from the EMPLOYEE table without creating an interim table to produce the dataset:Dataset:----------------------| NAME | TOTAL_HRS |----------------------| Joe | 6 |----------------------| Bob | 14 |----------------------
"
Combining data from 2 tables with 1 query using GROUP BY 
Posted - 2006-11-29 : 08:08:22
