SQL Server 2005 - 查询帮助(SQL Server 2005 - Query Help)

 我试图使用以下示例表编写查询： 
 
 公司 
 
 
 
 CompanyID 
 
 
 名称 
 
 
 县 
 
 
 CompanyRelation 
 
 
 
 ParentCompanyID 
 
 
 ChildCompanyID 
 
 
 CompanySpecialty 
 
 
 
 CompanyID 
 
 
 专业 
 
 
 使用CompanyRelation表是因为每个公司下面都有许多子公司，每个子公司可以有很多公司。 
 
 理想情况下，我希望能找到的是某个县和/或某个专业的所有公司。 现在，通常情况下，我会加入Company到CompanySpecialty并过滤这两个字段，但这里是棘手的：子公司没有分配给他们的县，所以如果我过滤县，所有的子公司将是排除。 
 
 如果我将公司过滤到一个县，我想显示与该公司关联的所有子公司，无论子公司没有地址。 
 
 为了解决这个问题，我必须引入CompanyRelation表。 
 
 示例数据： 
 
CompanyID---Name---County
1-----------ABC----King
2-----------BCD----Pierce
3-----------DEF----NULL
4-----------EFG----NULL

ParentCompanyID---ChildCompanyID
1-----------------1
1-----------------3
2-----------------2
2-----------------4

CompanyID---Specialty
1-----------Vehicles
2-----------Vehicles
3-----------Vehicles
4-----------Vehicles
 
 使用这些数据，说我想找到金县所有处理车辆的公司。 在我的结果中，我希望看到公司1和公司3。 
 
 如何编写查询来完成此任务？ 

I am trying to write a query using the following example tables:
 
Company
 
 
 
CompanyID
 
 
Name
 
 
County
 
 
CompanyRelation
 
 
 
ParentCompanyID
 
 
ChildCompanyID
 
 
CompanySpecialty
 
 
 
CompanyID
 
 
Specialty
 
 
The CompanyRelation table is used because each company can have many subcompanies under it and each subcompany can have many companies over it.
 
Ideally, what I want to be able to find is all companies by a certain county and/or by a certain specialty. Now, normally, I would just join Company to CompanySpecialty and filter on those two fields, but here is where it gets tricky: the subcompanies don't have counties assigned to them, so if I filter on the county, all the subcompanies will be excluded. 
 
If I filter the companies down to a county, I would like to display all subcompanies that are associated to that company, regardless of the fact that the subcompany does not have an address.
 
To get around this, I have to bring in the CompanyRelation table. 
 
Example data:
 
CompanyID---Name---County
1-----------ABC----King
2-----------BCD----Pierce
3-----------DEF----NULL
4-----------EFG----NULL

ParentCompanyID---ChildCompanyID
1-----------------1
1-----------------3
2-----------------2
2-----------------4

CompanyID---Specialty
1-----------Vehicles
2-----------Vehicles
3-----------Vehicles
4-----------Vehicles
 
Using this data, say I want to find all companies in King county that deal with vehicles. In my results, I would expect to see Company 1 and Company 3.
 
How can I write a query to accomplish this?

原文：https://stackoverflow.com/questions/6710199