Re: Table Join Question

From: <sriniv79_at_my-deja.com>
Date: Mon, 21 Aug 2000 19:59:05 GMT
Message-ID: <8ns1ls$6oh$1_at_nnrp1.deja.com>


Thanks Joe, but no thanks :))

As you have said, my example was faulty. Sorry about that. I will also post the DDL next time!!

But I have inherited a huge 60+ table legacy schema, where I could do the joins either way. If it were the case, does your post imply I should always join back to the table which has the primary key?

-S

In article <8nrgk6$h9e$1_at_nnrp1.deja.com>,   Joe Celko <71062.1056_at_compuserve.com> wrote:
>
> >> 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.
>

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

Original text of this message