多对多的关系 - SQL [英] Many to many relationship - SQL

问题描述

我有2个表，表1有projectID（主键），项目名称等。

表2有ProjectID，BusinesscaseID等，但是这个表中没有主键。



我必须过滤所有与项目ID相关的商业案例ID在Table1（这称为活动项目）上，现在取出所有活动项目的业务案例，并找出与这些业务案例相关的其他项目ID。



BC（BCID，BCNAME，PROJECTID） - 没有主键

AP（ProjectID，ProjectName） - 项目ID是主键



1项目有很多商业案例和1个商业案例有很多项目（多对多关系）



有人可以帮我吗？



谢谢



我的尝试：



我尝试使用MS ACCESS但没有得到我想要的结果。

Hi,

I have 2 tables where, table 1 has got projectID(Primary Key), Project Name etc..
table 2has got ProjectID, BusinesscaseID, etc but there is no primary key in this table.

I have to Filter all the Business case ID's which are related to the Project ID on the Table1 (This is called active project), now take out all active project's business cases and find out the other project id's which are related to these business cases.

BC (BCID, BCNAME, PROJECTID)- no primary key
AP (ProjectID,ProjectName) - Project ID is primary key

1 project has got many business cases and 1 Business case has got many project (Many to many relationship)

Can anyone help me with this please?

Thank you

What I have tried:

I tried using MS ACCESS but not having the results I wanted.

解决方案

建筑必须有良好的基础，否则就有可能崩溃。在软件开发中，我将数据库组件比作建筑物的基础。

您必须正确设计数据库，否则您将在后续开发中面临许多困难，例如此问题。

首先，要在2个表之间建立多对多关系，您需要第三个表来代理它。例如，

A building must have a good foundation, else it risks collapse. In software development, I liken the database component to the foundation of a building.
You must design the database correctly, else you will face many difficulties, such as this one, in subsequent development.
Firstly, to establish many-to-many relationship between 2 tables, you need a third table to broker it. For example,

table1                      table3                       table2
projectID(primary key)+---< projectID(primary key)      bizcaseName
projectName                 bizcaseID(primary key) >---+bizcaseID (primary key)
otherfields                                             otherfields

其次，每个表必须有一个主键，对于table3,2个字段是复合主键。

了解更多：

1. 数据库设计简介 [ ^ ]

2. 数据库规范化中的1NF，2NF，3NF和BCNF | DBMS教程| Studytonight [ ^ ]

在修复数据库设计之前不要继续前进。

Secondly, every table must have a primary key, for table3, the 2 fields are the composite primary key.
Learn more:
1. Introduction to database design[^]
2. 1NF, 2NF, 3NF and BCNF in Database Normalization | DBMS Tutorial | Studytonight[^]
Do not move forward until you fix your database design.

请参阅下面有关sql server连接的文章



T-SQL加入类型| SQL Server Pro中的T-SQL内容 [ ^ ]



此描述连接表和各种关系模型时使用的代码

See the below article on joins for sql server

T-SQL Join Types | T-SQL content from SQL Server Pro[^]

This description the code to use when joining tables and the various relationship models

这篇关于多对多的关系 - SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！