Re: Table Join Question

From: Joe Celko <71062.1056_at_compuserve.com>
Date: Mon, 21 Aug 2000 15:08:08 GMT
Message-ID: <8nrgk6$h9e$1_at_nnrp1.deja.com>

>> In have three Tables .. <<

Your scheam is wrong -- employee refrences shoudl not be in a projects table:

CREATE TABLE Employees
(emp_id INTEGER NOT NULL PRIMARY KEY,
 emp_name CHAR(25) nOT NULL,
   ...);

CREATE TABLE Projects
(project_id INTEGER NOT NULL PRIMARY KEY,  project_name CHAR(25) NOT NULL,
  ...);

CREATE TABLE Roles
(emp_id INTEGER NOT NULL

        REFERENCES Employees (emp_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
 project_id INTEGER NOT NULL
        REFERENCES Projects (project_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

 role_description CHAR(255) nOT NULL,
 PRIMARY KEY (emp_id, project_id));

>> To get the role description of an Employee in a Project, I would Join
 EMP, PROJECT and ROLE. <<

With the right schema, you have only one possible way to write the query.

SELECT emp_id, emp_name, project_id, project_name, role_description   FROM Employees AS E1, Projects AS P1, Roles AS R1  WHERE E1.emp_id = R1 emp_id
   AND P1.proejct_id = R1.project_id;

Your problem was you had one atomic fact in two places -- "employee X works on Project Y" instead of keeping it in one table.

--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Aug 21 2000 - 17:08:08 CEST