Help with JOIN Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with JOIN Statement

Author

Topic

wco5002
Starting Member

10 Posts

Posted - 2008-04-24 : 19:06:56

Hi, I'm doing a short lab assignment for a college SQL Server course, and I'm a little stuck up on one of the SELECT statements. Here's the questions: Create a SELECT statement that will return a list of all your employees, their project and the hours they have logged.

Here's the SQL Code:

CREATE TABLE project_employee (
  employee_id               INT,
  employee_name             VARCHAR(40),
  employee_hire_date        DATETIME,
  employee_termination_date DATETIME,
  employee_billing_rate     INT,
  CONSTRAINT project_employee_pk
    PRIMARY KEY (employee_id)
);

CREATE TABLE project (
  project_id              INT,
  project_name            VARCHAR(40),
  project_budget          INT,
  CONSTRAINT project_pk
    PRIMARY KEY (project_id)
);

CREATE TABLE project_hours (
  project_id              INT,
  employee_id             INT,
  time_log_date           DATETIME,
  hours_logged           INT,
  dollars_charged         INT,
  CONSTRAINT project_hours_pk
    PRIMARY KEY (project_id, employee_id, time_log_date),
  CONSTRAINT proj_hours_fkto_projectemployee
    FOREIGN KEY (employee_id) REFERENCES project_employee,
  CONSTRAINT proj_hours_fkto_project
    FOREIGN KEY (project_id) REFERENCES project
);

INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1001, 'Corporate Web Site',1912000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1002, 'Year 2000 Fixes',999998000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1003, 'Accounting System Implementation',897000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1004, 'Data Warehouse Maintenance',294000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1005, 'TCP/IP Implementation',415000);



INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (101, 'Jonathan Gennick','15-Nov-1961',null,169);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (102, 'Jenny Gennick','16-Sep-1964','5-May-1998',135);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (104, 'Jeff Gennick','29-Dec-1987','1-Apr-1998',99);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (105, 'Horace Walker','15-Jun-1998',null,121);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (107, 'Bohdan Khmelnytsky', '2-Jan-1998',null,45);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (108, 'Pavlo Chubynsky','1-Mar-1994','15-Nov-1998',220);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (110, 'Ivan Mazepa', '4-Apr-1998','30-Sep-1998',84);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (111, 'Taras Shevchenko', '23-Aug-1976',null,100);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (112, 'Hermon Goche', '15-Nov-1961','4-Apr-1998',70);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (113, 'Jacob Marley', '3-Mar-1998','31-Oct-1998',300);


INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-JAN-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-JAN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-JAN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-FEB-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-FEB-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-MAR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-MAR-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-MAR-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-APR-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-APR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-MAY-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-MAY-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-MAY-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-JUN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-JUN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-JUL-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-JUL-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-JUL-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-AUG-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-AUG-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-SEP-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-SEP-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-SEP-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-OCT-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-OCT-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-NOV-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-NOV-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-NOV-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-DEC-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-DEC-1998',3.00,507.00);

And here's the SELECT statement that I've wrote so far, but I'm not sure how to get the JOIN working correctly. Tips?

SELECT
  EMPLOYEE_NAME AS 'Employee''s Name', PROJECT_NAME AS 'Project Name', HOURS_LOGGED AS 'Hours Logged'
FROM
  PROJECT_EMPLOYEE, PROJECT, PROJECT_HOURS
WHERE
  PROJECT_HOURS.PROJECT_ID = PROJECT.PROJECT_ID AND PROJECT_EMPLOYEE.EMPLOYEE_ID = PROJECT_HOURS.EMPLOYEE_ID;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 23:53:06

Analyse the satement you have written. It gives you the details of all the instances of all logged hours done by all employees for each project he/she worked. Now you need only the total hours logged rather than the individual instances. Thinking in this lines you will realise you need a higher level i.e grouped version out of this data showing only the total hours for a project. This is exactly what GROUP BY clause does for you. For getting total hours you need to sum up all the instances of hours put for that project.
Using this info try to modify your query to get your result. Look into books online for syntax of GROUP BY and SUM aggregate function.

wco5002
Starting Member

10 Posts

Posted - 2008-04-25 : 17:52:17

I've tried using this statement now:


SELECT
  EMPLOYEE_NAME AS 'Employee Name', SUM(HOURS_LOGGED) AS 'Hours Logged'
FROM
  PROJECT_EMPLOYEE, PROJECT_HOURS
GROUP BY
  HOURS_LOGGED, EMPLOYEE_NAME

But I'm still having trouble, any pointers?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-26 : 01:58:59

quote:
Originally posted by wco5002

I've tried using this statement now:

SELECT
EMPLOYEE_NAME AS 'Employee Name', SUM(HOURS_LOGGED) AS 'Hours Logged'
FROM
PROJECT_EMPLOYEE, PROJECT_HOURS
GROUP BY
HOURS_LOGGED, EMPLOYEE_NAME

But I'm still having trouble, any pointers?

You havent specified the condition on which the join is to be done. Also you want the sum of hours logged so no need of including it in group by. So your query becomes:-

SELECT pe.employee_name,p.project_name,SUM(ph.hours_logged)
FROM project_employee pe
INNER JOIN project_hours ph
ON ph.employee_id=pe.employee_id
INNER JOIN project p
ON p.project_id =ph.project_id
GROUP BY pe.employee_name,p.project_name

Now analyse this and see how you were able to get the result.

wco5002
Starting Member

10 Posts

Posted - 2008-04-26 : 17:39:43

I can see where you went with this, but this is only partly right. I need the total hours for the employee, not just the total for each project.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-27 : 01:37:01

quote:
Originally posted by wco5002

I can see where you went with this, but this is only partly right. I need the total hours for the employee, not just the total for each project.

Ok . then drop the project name from group by

SELECT pe.employee_name,SUM(ph.hours_logged)
FROM project_employee pe
INNER JOIN project_hours ph
ON ph.employee_id=pe.employee_id
INNER JOIN project p
ON p.project_id =ph.project_id
GROUP BY pe.employee_name

whatever quantity you want to aggregate for, just put it in group by and apply proper aggregate function for the reqd field.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources