英文:
Oracle Many-to-Many relationship
问题
我需要一些帮助建立多对多关系。
我阅读了一些文章，看起来我需要使用一个包含两个主要表的ID的交叉表来创建一种一对多的映射。
这是我尝试做的一个示例：
雇员列表和基本信息
雇员表
项目列表和项目站点位置
项目表
项目和员工之间的链接表
链接表
我创建了链接表并输入了数据。基于此，Bob Brown仅分配给Project A，而David Doe分配给Project A和C。从另一个角度看，Project A有三名被分配的员工 - Bob Brown，David Doe和Sam Smith。
在我尝试查找Janet Jones的信息时，它工作得很好。由于她没有项目分配，链接表中没有条目，我不能用（null）PROJ_ID添加条目，我该怎么办？
英文:
I need some help setting up a many-to-many relationship.
I read through articles, and it looks like I need to use an intersection table that contains the IDs from the two main tables to create a sort of 1-many mapping.
Here's an example of what I am trying to do:
List of Employees and basic information
EMPLOYEE TABLE
List of Projects and where the project site is located
PROJECT TABLE
The link table between projects and the employees
LINK TABLE
I created the LINK table and input the data. Based on this, Bob Brown is assigned only to Project A while David Doe is assigned to Project A and C. Looking the other way, Project A has three employees assigned - Bob Brown, David Doe, and Sam Smith.
It works fine until I try to find information on Janet Jones. Since she doesn't have a project assignment, there is no entry in the LINK table and I cannot add one with a (null) PROJ_ID, what do I do?
答案1
得分: 2
如果你的意思是：
> 如何将没有项目的人输入链接表？
你不需要这样做，因为他们没有参与项目，所以没有需要链接的内容，因此不需要在链接表中输入条目。
在链接表中的第5和第6行不应存在，因为当关系的一侧为 NULL 时，没有链接。只需省略它们并使用 OUTER JOIN 或 NOT EXISTS 来查找那些员工/项目。
例如，如果你想查找在链接表中没有条目的员工：
SELECT *
FROM employee e
WHERE NOT EXISTS (
SELECT 1
FROM link l
WHERE l.emp_id = e.emp_id
);
或者：
SELECT *
FROM employee e
LEFT OUTER JOIN link l
ON l.emp_id = e.emp_id
WHERE l.emp_id IS NULL;
如果你想要所有的详细信息，然后使用 FULL OUTER JOIN：
SELECT e.*, p.*
FROM link l
FULL OUTER JOIN employee e
ON l.emp_id = e.emp_id
FULL OUTER JOIN project p
ON l.proj_id = p.proj_id
英文:
If you mean:
> how do I enter a person without a project into the link table?
You do not, they are not involved in a project so there is nothing to link to so they do not need an entry in the link table.
Rows 5 and 6 in your link table should not be in the link table as there is no link when one side of the relationship is NULL. Just omit them and use OUTER JOINs or NOT EXISTS to find those employees/projects.
For example, if you want to look for employees where there is no entry in the link table then:
SELECT *
FROM employee e
WHERE NOT EXISTS (
SELECT 1
FROM link l
WHERE l.emp_id = e.emp_id
);
or:
SELECT *
FROM employee e
LEFT OUTER JOIN link l
ON l.emp_id = e.emp_id
WHERE l.emp_id IS NULL;
If you want all the details then use FULL OUTER JOIN:
SELECT e.*, p.*
FROM link l
FULL OUTER JOIN employee e
ON l.emp_id = e.emp_id
FULL OUTER JOIN project p
ON l.proj_id = p.proj_id
答案2
得分: 0
简而言之，什么也没有！一切都在按预期进行。由于珍妮特没有项目，她不会出现在关联表中，直到分配项目给她。
另外，你的关联表不需要自己的主键。主键是由员工表和项目表的主键组成的复合键，当添加条目时形成的。
英文:
In short, nothing! It's working as expected. Since Janet does not have a project, she would not be in the associative table until she is assigned a project.
Also, your associative table does not need it's own primary key. The primary key is the composite key that is formed from the primary keys from the employee and project tables when an entry is added.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论