mysql - SQL检索具有多对多关系的相关记录

Question

我正在构建一个用于注册机构之间协议(protocol)的应用程序。这些协议(protocol)可能包括 2 个以上的合作伙伴。因此，我很快就放弃了将part1和partner2放在contracts表中的想法。

当前设计是(注意:简化问题):

Table Institutes: ID, Name , ..

Table Contract_institutes: ContractID, InstituteID

Table Contracts: ID, Title, ...

假设您认识一个合作伙伴，我将如何显示包括所涉及合作伙伴在内的所有契约(Contract)的列表:用户已登录，并希望查看其机构拥有的所有契约(Contract)以及契约(Contract)中的所有合作伙伴;例如:

• 契约(Contract) 1:(标题)Institute1Name、Institute2Name
• 契约(Contract)2:(标题)Institute1Name、Institute2Name、Institute3Name
• 契约(Contract) 3:(标题)Institute1Name

我可以先获取所有合约ID

select *fields* 
from Contracts
  left join Contract_institutes on Contracts.ID = Contract_institutes.ContractID
  where  Contract_institutes.InstituteID = *SomeValue*

然后对每个合约进行单独的查询(或者在查询中使用 IN 语句)获取所有相关机构，并使用大量的 foreach php 循环进行格式化。不漂亮，而且可能效率不高。

必须有一个更好的方法来做到这一点，并在单个 sql 语句中获取列表。有人能帮我吗？

理想情况下，我得到的输出行为:[契约(Contract) ID][InstituteID][Institute.Name]。我可以在输出的每个合约 View 中轻松修改它。

PS:- 这是应用程序的设计阶段:数据库是空的，可以根据需要进行修改。

Answer 1

select C.ID, I.ID, I.Name
from Contracts C 
  join Contract_institutes CI on C.ID = CI.ContractID
  join Institutes I on I.ID=CI.InstituteId
where CI.InstituteID <> *SomeValue*
    and CI.ContractID in (select CI2.ContractId 
        from Contract_institutes CI2
        where CI2.InstituteID = *SomeValue*)