I have an application based on 4 MySQL tables:

persons
------------
id              -- Primary key, unique
name
entity_id       -- foreign key => entities

entities
------------
id              -- Primary key, unique
name
company_id      -- foreign key => companies

companies
------------
id              -- Primary key, unique
name

persons_linked_companies -- join table : persons <=> companies
--------------------------
id              -- Primary key, unique
persons_id      -- foreign key => persons
companies_id    -- foreign key => companies

1. Each "person" belong to an "entity"
2. Each "entity" belong to a "company"
3. A "person" can only have one "entity"
4. An "entity" can only have one "company"
5. A "person" can be linked to one or more third parties (meaning other companies). For this there is a join table called "persons_linked_companies"
6. A person can have multiple linked companies, but a person shouldn't be linked to his own company

I can't figure out what kind of subquery/join I should issue to get the following data:

I need to select entries in the join table "persons_linked_companies" to get all persons whose linked company is the same has the company they belong to (because of bullet point 6).

Pseudo code below:

select id from persons_linked_companies where companies_id = (select id from companies where persons.entity.company_id = persons_linked_companies.companies_id)

CodePudding user response：

Besides using aliases. you can join all tables

But this would only yield a result, if you entered such a row in the table persons_linked_companies, which should be not be done by your rule 6

SELECT 
    id
FROM
    persons_linked_companies pcl
WHERE
    companies_id = (SELECT 
            c.id
        FROM
            companies c
            INNER JOIN entities e ON e.company_id = c.id
            INNER JOIN persons p ON p.entity_id = e.id
        WHERE
            p.id = pcl.persons_id)