SQL - 两个外连接

SQL - Two Outer Joins

提问by user70192

I have a table that represents a list of countries. I have another table that represents a list of states. I have another table that represents a list of provinces. Because of poor data definition, some states are actually in the province table and vice-versa. Regardless, each province and state is associated with a country.

我有一个代表国家/地区列表的表格。我有另一个代表状态列表的表。我有另一个代表省份列表的表格。由于数据定义不佳，有些州实际上在省表中，反之亦然。无论如何，每个省和州都与一个国家相关联。

I need to essentially do a double left outer join. My question is, how do I do this? Here is what I am currently trying:

我基本上需要做一个双左外连接。我的问题是，我该怎么做？这是我目前正在尝试的：

select
  c.Name as 'CountryName',
  ISNULL(p.[Name], '') as 'ProvinceName',
  ISNULL(s.[Name], '') as 'StateName'
from
  Country c 
    left outer join [Province] p on p.[CountryID]=c.[ID]
    left outer join [State] s on s.[CountryID]=c.[ID]

Please note that I need to do something comparable to two left outer joins. This is a simplified version of the query I'm trying to do. Thank you for your help!

请注意，我需要做一些与两个左外连接相当的事情。这是我正在尝试执行的查询的简化版本。感谢您的帮助！

回答by cletus

You can do it the way you've stated. There's nothing wrong with that. I wouldn't necessarily replace NULLs with empty strings though. Is there a reason you're doing that?

你可以按照你说的方式去做。没有什么不对的。不过，我不一定会用空字符串替换 NULL。你有理由这样做吗？

What you have to be aware of when doing what are essentially two one-to-many joins is that the results are multiplicative. By this I mean that if for a country there are 3 entries in the province table and 4 in the state table you'll get back 12 rows for that country.

在进行本质上是两个一对多连接的操作时，您必须注意的是结果是乘法的。我的意思是，如果一个国家的省表中有 3 个条目，州表中有 4 个条目，您将返回该国家/地区的 12 行。

It might be more appropriate to do a UNION in these circumstances. For example:

在这些情况下执行 UNION 可能更合适。例如：

SELECT
  c.Name AS 'CountryName',
  '' AS 'ProvinceName',
  ISNULL(s.[Name], '') AS 'StateName'
FROM Country c 
LEFT OUTER JOIN [Province] p ON p.[CountryID]=c.[ID]
UNION ALL
SELECT
  c.Name AS 'CountryName',
  ISNULL(p.[Name], '') AS 'ProvinceName',
  '' AS 'ProvinceName'
FROM Country c 
LEFT OUTER JOIN [State] s ON s.[CountryID]=c.[ID]

as just one possibility. It really depends on what your data looks like and what you want the end result to be.

作为一种可能性。这实际上取决于您的数据是什么样子以及您希望最终结果是什么。

回答by hobodave

A LEFT OUTER JOIN is just a LEFT JOIN, the syntax is simply:

A LEFT OUTER JOIN 只是一个 LEFT JOIN，语法很简单：

SELECT c.Name AS CountryName
p.Name AS ProvinceName
s.Name AS StateName
FROM Country c
LEFT JOIN Province p ON p.CountryID = c.ID
LEFT JOIN State s ON s.CountryID = c.ID

回答by lc.

I'm guessing you want the provinces and states concatenated in one table. For this, you'll want the union operator. I've also added a column RegionTypewhich is 0 for a province and 1 for a state. You might want to apply this pattern as a way to differentiate between the two in your final query.

我猜您希望将省和州连接在一张表中。为此，您需要联合运算符。我还添加了一个列RegionType，其中一个省为 0，州为 1。您可能希望应用此模式作为在最终查询中区分两者的一种方式。

Try something like:

尝试类似：

SELECT c.Name as CountryName, p.Name as RegionName, 0 as RegionType
FROM Country c
LEFT OUTER JOIN Province p on c.ID = p.CountryID
UNION ALL
SELECT c.Name as CountryName, s.Name as RegionName, 1 as RegionType
FROM Country c
LEFT OUTER JOIN State s on s.ID = p.CountryID

回答by Jonathan Fingland

while your implementation covers states and provinces, what about territories (e.g. Canada has both, regions and territories) ? might be better off with a single table for "administrative regions" and another table for region types containing "state", "province", "territory", etc. Most of the time you aren't going to care what kind of region it is, just the country id, region id, and region name.

虽然您的实施涵盖州和省，但领土（例如加拿大有地区和领土）呢？使用一个用于“行政区域”的表和另一个用于包含“州”、“省”、“领土”等区域类型的表可能会更好。大多数时候你不会关心它是什么类型的区域就是，只有国家 ID、地区 ID 和地区名称。

select
c.Name as 'CountryName',
ISNULL(r.[Name], '') as 'RegionName'
from
Country c 
left outer join [Regions] r on r.[CountryID]=c.[ID]

回答by Rob Farley

Sounds like your problem is that you have two tables that need to be treated as one - ie, UNION ALL. So how about:

听起来您的问题是您有两个表需要被视为一个表 - 即 UNION ALL。那么怎么样：

SELECT 
    c.Name as Country
   ,ps.Name as StateOrProvince
FROM        Country c
LEFT JOIN   (SELECT CountryID, Name FROM Province UNION ALL SELECT CountryID, Name FROM State) ps
    ON ps.CountryID = c.ID
;

Rob

抢

回答by Todd K

I finally was able to get this figured out. You can stack as many outer joins as you want, the key is to put all of the where qualifiers on the outer join line so that you don't lose all of the nulls. See below where all of the qualifiers for agldimension are included on the same line, NOT in the where statement.

我终于能够弄清楚这一点。您可以根据需要堆叠尽可能多的外连接，关键是将所有 where 限定符放在外连接线上，这样您就不会丢失所有空值。请参阅下文，其中 agldimension 的所有限定符都包含在同一行中，而不是包含在 where 语句中。

select distinct
act.account, 
act.[description],
rul.[description],
att1.att_name,
att2.att_name,
att3.att_name,
att4.att_name,
att5.att_name,
att6.att_name,
att7.att_name
from aglaccounts act, aglrules rul  
full outer join agldimension att1 on rul.att_1_id = att1.attribute_id and att1.[status] = 'N' and att1.client = 'PH' 
full outer join agldimension att2 on rul.att_2_id = att2.attribute_id and att2.[status] = 'N' and att2.client = 'PH'
full outer join agldimension att3 on rul.att_3_id = att3.attribute_id and att3.[status] = 'N' and att3.client = 'PH'
full outer join agldimension att4 on rul.att_4_id = att4.attribute_id and att4.[status] = 'N' and att4.client = 'PH'
full outer join agldimension att5 on rul.att_5_id = att5.attribute_id and att5.[status] = 'N' and att5.client = 'PH'
full outer join agldimension att6 on rul.att_6_id = att6.attribute_id and att6.[status] = 'N' and att6.client = 'PH'
full outer join agldimension att7 on rul.att_7_id = att7.attribute_id and att7.[status] = 'N' and att7.client = 'PH'
where 
act.account_rule = rul.account_rule and act.client = 'PH' and act.[status] = 'N'