Joining two tables with many to many relationship

How can I join two tables with many to many relationship? Any idea?
On Mon, 27 Sep 2004 12:27:47 +0300, Comfort wrote:

>How can I join two tables with many to many relationship? Any idea?
>
Hi Comfort,
A many-to-many relationship is usually implemented through a linking
table. For example, let's say each employee can work on multiple proects
and each project can be worked on by multiple employees. Your tables would
look somewhat like this:
CREATE TABLE Personnel (EmpID char(10) NOT NULL,
other columns,
PRIMARY KEY (EmpID)
)
CREATE TABLE Projects (ProjID int NOT NULL,
other columns,
PRIMARY KEY (ProjID)
)
CREATE TABLE Assignments (EmpID char(10) NOT NULL,
ProjID int NOT NULL,
PRIMARY KEY (EmpID, ProjID),
FOREIGN KEY (EmpID) REFERENCES Personnel,
FOREIGN KEY (ProjID) REFERENCES Projects
)
This structure also allows you to store extra information about the
relations (e.g. when was the employee assigned to a project, wat role will
(s)he play in the project, etc)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Can u please tell the SQL Select syntax for joining two tables with many to many relationships. The two tables don't hold the foreign keys, but the third table as u said, holds them. Then how to perform join?
Thanx
A.P

Quote:

Originally posted by Hugo Kornelis
On Mon, 27 Sep 2004 12:27:47 +0300, Comfort wrote:

>How can I join two tables with many to many relationship? Any idea?
>
Hi Comfort,
A many-to-many relationship is usually implemented through a linking
table. For example, let's say each employee can work on multiple proects
and each project can be worked on by multiple employees. Your tables would
look somewhat like this:
CREATE TABLE Personnel (EmpID char(10) NOT NULL,
other columns,
PRIMARY KEY (EmpID)
)
CREATE TABLE Projects (ProjID int NOT NULL,
other columns,
PRIMARY KEY (ProjID)
)
CREATE TABLE Assignments (EmpID char(10) NOT NULL,
ProjID int NOT NULL,
PRIMARY KEY (EmpID, ProjID),
FOREIGN KEY (EmpID) REFERENCES Personnel,
FOREIGN KEY (ProjID) REFERENCES Projects
)
This structure also allows you to store extra information about the
relations (e.g. when was the employee assigned to a project, wat role will
(s)he play in the project, etc)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)