CODEWITHSUNDEEP
mysqlnormalization
Jelle Ferwerda
Jelle Ferwerda

Reputation: 1229

SQL to retrieve related records with many to many relation

I am building an application for registration of agreements between institutes. These agreements may include more than 2 partners. As such, I quickly dropped the idea of having part1 and partner2 in a contracts table.

Current design is (Note: simplified for question):

Table Institutes: ID, Name , ..

Table Contract_institutes: ContractID, InstituteID

Table Contracts: ID, Title, ...

How would I go about showing a list of all contracts including the involved partners, assuming you know one partner: A user is logged in, and wants to see all the contracts that his institute has, and all the partners in the contract; e.g.:

I could first get all the contracts IDs

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

And then get all the related institutes with a separate query for each contract (Or using an IN statement in the query), and use a lot of foreach php loops to format. Not pretty, and probably not efficient.

There must be a better way to do this, and get the list in a single sql statement. Can someone help me?

Ideally, I get output rows with: [contract ID][InstituteID][Institute.Name]. I can easily modify this in a per-contract view in the output.

PS: - This is design phase of the application: The database is empty and can be modified to needs.

Upvotes: 0

Views: 41

Answers (1)

tzunghaor
tzunghaor

Reputation: 1035

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*)

Upvotes: 1

Related Questions