I've created 3 views with identical columns- Quantity, Year, and Variety. I want to join all three tables on year and variety in order to do some calculations with quantities.
我创建了3个相同列的视图 - 数量,年份和品种。我想在年份和品种上加入所有三个表格,以便对数量进行一些计算。
The problem is that a particular year/variety combo does not occur on every view.
问题是每个视图都不会出现特定的年份/品种组合。
I've tried queries like :
我尝试过如下查询:
SELECT
*
FROM
a
left outer join
b
on a.variety = b.variety
left outer join
c
on a.variety = c.variety or b.variety = c.variety
WHERE
a.year = '2015'
and b.year = '2015'
and a.year= '2015'
Obviously this isn't the right solution. Ideally I'd like to join on both year and variety and not use a where statement at all.
显然这不是正确的解决方案。理想情况下,我想加入年份和品种,而不是使用where声明。
The desired output would be put all quantities of matching year and variety on the same line, regardless of null values on a table.
无论表上的空值如何,所需的输出都会将所有匹配的年份和品种数量放在同一行上。
I really appreciate the help, thanks.
我非常感谢你的帮助,谢谢。
1 个解决方案
#1
You want a full outer join, not a left join, like so:
你想要一个完整的外连接,而不是左连接,如下所示:
Select coalesce(a.year, b.year, c.year) as Year
, coalesce(a.variety, b.variety, c.variety) as Variety
, a.Quantity, b.Quantity, c.Quantity
from tableA a
full outer join tableB b
on a.variety = b.variety
and a.year = b.year
full outer join tableC c
on isnull(a.variety, b.variety) = c.variety
and isnull(a.year, b.year) = c.year
where coalesce(a.year, b.year, c.year) = 2015
The left join you are using won't pick up values from b or c that aren't in a. Additionally, your where clause is dropping rows that don't have values in all three tables (because the year in those rows is null, which is not equal to 2015). The full outer join will grab rows from either table in the join, regardless of whether the other table contains a match.
您正在使用的左连接不会从b或c中选取不在a中的值。此外,您的where子句正在删除所有三个表中没有值的行(因为这些行中的年份为null,不等于2015)。无论另一个表是否包含匹配项,完整外部联接将从联接中的任一表中获取行。
#1
You want a full outer join, not a left join, like so:
你想要一个完整的外连接,而不是左连接,如下所示:
Select coalesce(a.year, b.year, c.year) as Year
, coalesce(a.variety, b.variety, c.variety) as Variety
, a.Quantity, b.Quantity, c.Quantity
from tableA a
full outer join tableB b
on a.variety = b.variety
and a.year = b.year
full outer join tableC c
on isnull(a.variety, b.variety) = c.variety
and isnull(a.year, b.year) = c.year
where coalesce(a.year, b.year, c.year) = 2015
The left join you are using won't pick up values from b or c that aren't in a. Additionally, your where clause is dropping rows that don't have values in all three tables (because the year in those rows is null, which is not equal to 2015). The full outer join will grab rows from either table in the join, regardless of whether the other table contains a match.
您正在使用的左连接不会从b或c中选取不在a中的值。此外,您的where子句正在删除所有三个表中没有值的行(因为这些行中的年份为null,不等于2015)。无论另一个表是否包含匹配项,完整外部联接将从联接中的任一表中获取行。