1

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)
donmelchior
  • 893
  • 3
  • 13
  • 31

1 Answers1

1

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)
nbk
  • 45,398
  • 8
  • 30
  • 47
  • thanks, I will test this asap. Note that rule 6 is now enforced during person creation in the application at the form level (it is a Python Web app build on Django framework) but this wasn't the case before. That's why now I need to detect and remove all old entries that don't satisfy rule 6 (person can't be linked to the company they belong to) – donmelchior Oct 11 '21 at 17:22
  • MySQL script above throws an error: "ERROR 1052 (23000) at line 1: Column 'id' in field list is ambiguous" – donmelchior Oct 12 '21 at 08:13
  • i added the table in the sub select. so try it again – nbk Oct 12 '21 at 10:11