总而言之,我有几家公司拥有多个网站和多个部门。每个部门都有一个或多个站点。我正在重用所有公司的部门表和站点表。基本上,我有一个全球性的部门和网站池,供每家公司选择。我的最终目标是拉出所有属于一个公司的部门的网站。
Company 1
|
+ Division 1
| |
| + Site 1
| + Site 2
|
+ Division 2
|
Company 2
|
+ Division 3
| |
| + Site 3
| + Site 2
|
+ Division 1
|公司
+----+-------------+
| id | name |
+----+-------------+
| 1 | company 1 |
+----+-------------+
| 2 | company 2 |
+----+-------------+ 站点
+----+-------------+
| id | name |
+----+-------------+
| 1 | site 1 |
+----+-------------+
| 2 | site 2 |
+----+-------------+
| 3 | site 3 |
+----+-------------+分部
+----+-----------------+
| 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 |
+----------+------------+我原本以为我可以选择所有受company.id和division.id约束的sites,但我没有这样的运气。我已经尝试了子查询:
select *
from sites
where id IN (select site from company_sites where company = 3)并加入:
select s.*
from sites s
inner join company_sites cs on s.id = cs.site
where cs.company = 3但这些结果只与company_site有关,而与division无关。我似乎想不出如何让company_divisions表参与进来。如下所示:
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如何添加额外的条件或查询,以确保用于选择company_sites中的site的同一company.id通过division.id与company_division中的company.id相关?
例如,给定company.id = 2和division.id = 3,我希望得到site 2和site 3的结果。
建设性的批评总是受欢迎的。
https://stackoverflow.com/questions/47616116
复制相似问题