双表a,b联查没有关问题加上c三表联查就出错了无法查询_Sql Server

双表a,b联查没问题加上c三表联查就出错了无法查询
双表ab联查语句：

select a.*,b.LY_SalesQty,b.LY_NetSales,b.LY_GrossSales from
(
SELECT storename
       ,dept
       ,dept+LEFT(itemcode,1) as GROUP_FAMILY
       ,dept+LEFT(itemcode,2) as FAMILY
       ,dept+LEFT(itemcode,3) as SUB_FAMILY
       ,sum(SalesQty) as CY_SalesQty
       ,sum(NetSales) as CY_NetSales
       ,sum(GrossSales) as CY_GrossSales
       ,avg(GMRate) as CY_GMRate
FROM [bjreport].[dbo].[DailySales201409]
where storename in ('075','076','081','011','016','018','019','027','030','040','041','042','043','046','048','051','053','054','060','061','062','063','077','087','089')
and salesdate between '2014-09-01' and '2014-09-24'
group by storename,dept,dept+LEFT(itemcode,1),dept+LEFT(itemcode,2),dept+LEFT(itemcode,3)
) a
left join
(
SELECT storename
       ,dept
       ,dept+LEFT(itemcode,1) as GROUP_FAMILY
       ,dept+LEFT(itemcode,2) as FAMILY
       ,dept+LEFT(itemcode,3) as SUB_FAMILY
       ,sum(SalesQty) as LY_SalesQty
       ,sum(NetSales) as LY_NetSales
       ,sum(GrossSales) as LY_GrossSales
       ,avg(GMRate) as LY_GMRate
FROM [bjreport].[dbo].[DailySales201309]
where storename in ('075','076','081','011','016','018','019','027','030','040','041','042','043','046','048','051','053','054','060','061','062','063','077','087','089')
and salesdate between '2013-09-01' and '2013-09-24'
group by storename,dept,dept+LEFT(itemcode,1),dept+LEFT(itemcode,2),dept+LEFT(itemcode,3)
)
b
on a.storename=b.storename
and a.dept=b.dept
and a.GROUP_FAMILY=b.GROUP_FAMILY
and a.FAMILY=b.FAMILY
and a.SUB_FAMILY=b.SUB_FAMILY

但是三表联查加上c表就出问题了请帮忙查看一下谢谢

select a.*,b.LY_SalesQty,b.LY_NetSales,b.LY_GrossSales,c.[FName] from
(
SELECT storename
       ,dept
       ,dept+LEFT(itemcode,1) as GROUP_FAMILY
       ,dept+LEFT(itemcode,2) as FAMILY
       ,dept+LEFT(itemcode,3) as SUB_FAMILY
       ,sum(SalesQty) as CY_SalesQty
       ,sum(NetSales) as CY_NetSales
       ,sum(GrossSales) as CY_GrossSales
       ,avg(GMRate) as CY_GMRate
FROM [bjreport].[dbo].[DailySales201409]
where storename in ('075','076','081','011','016','018','019','027','030','040','041','042','043','046','048','051','053','054','060','061','062','063','077','087','089')
and salesdate between '2014-09-01' and '2014-09-24'
group by storename,dept,dept+LEFT(itemcode,1),dept+LEFT(itemcode,2),dept+LEFT(itemcode,3)
) a
left join
(
SELECT storename
       ,dept
       ,dept+LEFT(itemcode,1) as GROUP_FAMILY
       ,dept+LEFT(itemcode,2) as FAMILY
       ,dept+LEFT(itemcode,3) as SUB_FAMILY
       ,sum(SalesQty) as LY_SalesQty
       ,sum(NetSales) as LY_NetSales
       ,sum(GrossSales) as LY_GrossSales

双表a,b联查没有关问题 加上c三表联查 就出错了 无法查询

双表a,b联查没有关问题加上c三表联查就出错了无法查询