MySQL根据附加表的条件选择行

Question

To summarize, I have several companies that own multiple sites and multiple divisions. Each division has one or more sites. I am reusing the division and site tables for all companies. I essentially have a global pool of divisions and sites for each company to pull from. My end goal is to pull all of the sites belonging to a division of a company.

Company 1  
   |  
   + Division 1  
   |      |  
   |      + Site 1  
   |      + Site 2  
   |
   + Division 2  
   |  
Company 2
   |
   + Division 3  
   |      |  
   |      + Site 3  
   |      + Site 2 
   | 
   + Division 1  
   |

company

+----+-------------+  
| id |    name     |  
+----+-------------+ 
| 1  |  company 1  |
+----+-------------+
| 2  |  company 2  |
+----+-------------+ 

sites

+----+-------------+  
| id |    name     |  
+----+-------------+ 
| 1  |   site 1    |
+----+-------------+
| 2  |   site 2    |
+----+-------------+
| 3  |   site 3    |
+----+-------------+

divisions

+----+-----------------+  
| id |      name       |  
+----+-----------------+ 
| 1  |   division 1    |
+----+-----------------+
| 2  |   division 2    |
+----+-----------------+
| 3  |   division 3    |
+----+-----------------+

company_divisions

+----------+--------------+  
| company  |   division   |  
+----------+--------------+ 
|     1    |       1      |
+----------+--------------+
|     1    |       2      |
+----------+--------------+
|     2    |       1      |
+----------+--------------+
|     2    |       3      |
+----------+--------------+

company_sites

+----------+------------+  
| company  |   site     |  
+----------+------------+ 
|     1    |     1      |
+----------+------------+
|     1    |     2      |
+----------+------------+
|     2    |     2      |
+----------+------------+
|     2    |     3      |
+----------+------------+

I originally thought I could select all of the sites constrained by a company.id and division.id but I have had no such luck. I have attempted sub queries:

select * 
from sites 
where id IN (select site from company_sites where company = 3)

and joins:

select s.* 
from sites s 
inner join company_sites cs on s.id = cs.site 
where cs.company = 3

but these results only relate to the company_site and not the division. I can't seem to figure out how to get the company_divisions table involved.. something like this:

select s.* 
from sites s 
inner join company_sites cs on s.id = cs.site 
inner join company_divisions cd on divisions.id = cd.division 
where cs.company = 2 AND cd.division = 3

How can I add an additional condition or query that ensures that the same company.id that was used to select the site in company_sites relates to a company.id in company_division via the division.id?

For example given company.id = 2 and division.id = 3 I would expect a result of site 2 and site 3.

Constructive criticism is always welcome.

Answer 1

For such tree structure, I would probably remove company_divisions and company_sites table and design it as such.

company
+----+-------------+  
| id |    name     |  
+----+-------------+ 
| 1  |  company 1  |
+----+-------------+
| 2  |  company 2  |
+----+-------------+
name should be unique, id is the primary key 

divisions
+----+-----------------+-------------+  
| id |      name       | company id  |
+----+-----------------+-------------+
| 1  |   division 1    |      1      |
+----+-----------------+-------------+
| 2  |   division 2    |      1      |
+----+-----------------+-------------+
| 3  |   division 3    |      2      |
+----+-----------------+-------------+
| 4  |   division 1    |      2      |
+----+-----------------+-------------+
id is the primary key, company id is foreign key referenced to company.id.

sites
+----+-------------+-------------+
| id |    name     | division id |
+----+-------------+-------------+
| 1  |   site 1    |      1      |
+----+-------------+-------------+
| 2  |   site 2    |      1      |
+----+-------------+-------------+
| 3  |   site 3    |      3      |
+----+-------------+-------------+
| 4  |   site 2    |      3      |
+----+-------------+-------------+
id is the primary key, division id  is foreign key referenced to divisions.id.

Using the query

SELECT 
    sites.name as `site`, 
    divisions.name as `division`, 
    company.name as `company` 
FROM sites
LEFT JOIN divisions ON sites.`division id` = divisions.id
LEFT JOIN company ON divisions.`company id` = company.id

would give

+-------------+------------+-----------+
|    site     |  division  |  company  |
+-------------+------------+-----------+
|   site 1    | division 1 | company 1 |
+-------------+------------+-----------+
|   site 2    | division 1 | company 1 |
+-------------+------------+-----------+
|   site 3    | division 3 | company 2 |
+-------------+------------+-----------+
|   site 2    | division 3 | company 2 |
+-------------+------------+-----------+

Filtering from here onward should be quite easy, just add WHERE conditions behind.