CODEWITHSUNDEEP
sql-serverjoinsubquery
toofaced
toofaced

Reputation: 151

How to retrieve a column from a nested sub-query in left join?

I have a store procedure in which I have to retrieve a column from the nested sub-query. I know how to get it if it is only nested once but for my this situation, the column I need to display is double nested. 

SELECT a.vendor,
a.last,
MaxInv
FROM 
    (
    SELECT vendor, day 
    FROM tblvendor) a

    LEFT JOIN (SELECT vendor, tblarinv.arinv, tblardetail.arddate
    Sum([MaxQty]*[ActualCost]) AS MaxInv,
    FROM tblarinv
    INNER JOIN tblardetail 
    ON tblarinv.id= tblardetail.id)    
group by a.vendor,a.last

In above example, I have to retrieve two columns from the left join LEFT JOIN (SELECT vendor,tblarinv.arinv, tblardetail.arddate. Originally, there is only vendor in the select statement but since I need tblarinv.arinv, tblardetail.arddate, I added them in that select statement inside left join. And then, when I try to add this field in first SELECT statement it doesn't recognize giving error 'Multi-part identifier could not be bound'. Can anyone help me out here?

Here is my edited query:

SELECT 
a.Vendor, 
,CASE WHEN sum(GP)<>0 THEN GP ELSE 0 END GP
,a.ThreeMonths
,CASE WHEN sum(GP)<>0 THEN GP/@WorkDaysElapsed ELSE 0 END AS AvgDailyGP
,CurrentInv
,MaxInv
,Inventory_Variance

FROM 
(Select Vendor, sum(GrossProfit) ThreeMonths, 
    (Select sum(GrossProfit) From dbo.MonthlySales with (nolock) Where EndOfMonth Between dateadd(m,-3,@First) and @First-1) as AllThreeMonths
    FROM MonthlySales with (nolock)
    Where EndOfMonth>=dateadd(m,-3,@First) 
    Group By Vendor
    Having sum(GrossProfit)<>0) a

LEFT JOIN (Select tblMaterial.Vendor, tblARDetail.ARInvoiceID, tblARDetail.ARInvoiceDetailID, tblARInv.ARInvoiceDate
,(Sum(SplitAmount))-(Sum((CASE WHEN SplitAmount<0 THEN -1*ABS([Quantity]) ELSE ABS([Quantity]) END)*dbo.fn_CalculatePrice_Decimal(tblARDetail.UnitCost,tblARDetail.ProductDiscountPct))) AS GP

            FROM tblARInv with (nolock)
            INNER JOIN tblARDetail with (nolock) ON tblARDetail.ARInvoiceID = tblARInv.ARInvoiceID
            INNER JOIN tblMaterial with (nolock) ON tblMaterial.MaterialID = tblARDetail.MaterialID
            INNER JOIN [dbo].[tblCust]
                ON [tblCust].[CustomerID] = [tblARInv].[CustomerID]
            Where tblARInv.Date>= '' + cast(Month(GetDate()) as varchar(2)) + '/1/' + cast(year(GetDate()) as varchar(4)) + ''
                AND [tblCust].[Status] != 8 --Internal
            Group By tblMaterial.Vendor, tblARDetail.ARInvoiceID, tblARDetail.ARInvoiceDetailID, tblARInv.ARInvoiceDate) 
            ThisMonth ON ThisMonth.Vendor=a.Vendor


Group By
a.Vendor
,GP
,a.ThreeMonths
,AllThreeMonths
,CurrentInv
,MaxInv
,Inventory_Variance
END

So, I need to retrieve tblARDetail.ARInvoiceID, tblARDetail.ARInvoiceDetailID, tblARInv.ARInvoiceDate which I added in above edited query.

Upvotes: 0

Views: 1278

Answers (1)

SqlZim
SqlZim

Reputation: 38043

There are alot of problems with your example query, but the first part is if*you do not need to "nest" joins, don't do it. (This is a bad habit some people pick up from Microsoft Access, where you have to use that sort of mess).

Your pseudo-query could be simplified down to something like this:

select 
    v.vendor
  , v.last
  , sum(d.[MaxQty]*d.[ActualCost]) as MaxInv
from tblvendor v
  left join tblarinv i
    on v.vendor = i.vendor
  left join tblardetail d
    on i.id= d.id
group by v.vendor, v.last

From your edited question, it does not look like those columns are nested, they are available in ThisMonth which is joined to a.

select 
    a.Vendor
  , case when sum(GP) <> 0 then GP else 0 end GP
  , a.ThreeMonths
  , case when sum(GP) <> 0 then GP / @WorkDaysElapsed else 0 end as AvgDailyGP
  , CurrentInv
  , MaxInv
  , Inventory_Variance
  /* Include the desired columns in the select */
  , ThisMonth.ARInvoiceID
  , ThisMonth.ARInvoiceDetailID
  , ThisMonth.ARInvoiceDate
from (
  select 
      Vendor
    , sum(GrossProfit) ThreeMonths
    , (
      select sum(GrossProfit)
      from dbo.MonthlySales with (nolock)
      where EndOfMonth between dateadd(month, - 3, @First) and @First - 1
      ) as AllThreeMonths
  from MonthlySales with (nolock)
  where EndOfMonth >= dateadd(month, - 3, @First)
  group by Vendor
  having sum(GrossProfit) <> 0
  ) a
left join (
  select 
       tblMaterial.Vendor
     , tblARDetail.ARInvoiceID
     , tblARDetail.ARInvoiceDetailID
     , tblARInv.ARInvoiceDate
     , (Sum(SplitAmount)) 
       - (Sum((case when SplitAmount < 0 then - 1 * ABS([Quantity]) else ABS([Quantity]) end) * dbo.fn_CalculatePrice_Decimal(tblARDetail.UnitCost, tblARDetail.ProductDiscountPct))) 
         as GP
  from tblARInv with (nolock)
    inner join tblARDetail with (nolock) 
     on tblARDetail.ARInvoiceID = tblARInv.ARInvoiceID
    inner join tblMaterial with (nolock) 
     on tblMaterial.MaterialID = tblARDetail.MaterialID
    inner join [dbo].[tblCust] 
       on [tblCust].[CustomerID] = [tblARInv].[CustomerID]
  where [tblCust].[Status] != 8 --Internal 
   -- and tblARInv.date >= '' + cast(Month(GetDate()) as varchar(2)) + '/1/' + cast(year(GetDate()) as varchar(4)) + ''
   /* easier way to get start of the month */
   and tblARInv.date >= dateadd(month, datediff(month, 0, getdate() )  , 0) 
  group by tblMaterial.Vendor
   , tblARDetail.ARInvoiceID
   , tblARDetail.ARInvoiceDetailID
   , tblARInv.ARInvoiceDate
  ) ThisMonth on ThisMonth.Vendor = a.Vendor
left join (
 select Vendor
  , Sum(case when [InStock] > [MaxQty] then [MaxQty] * [ActualCost] else [Instock] * [ActualCost] end) as CurrentInv
  , Sum([MaxQty] * [ActualCost]) as MaxInv
  , ((Sum(case when [InStock] > [MaxQty] then [MaxQty] * [ActualCost] else [Instock] * [ActualCost] end) / Sum(case when [MaxQty] * [ActualCost] <> 0 then [MaxQty] * [ActualCost] else 1 end)) - 0.75) / 0.75 as Inventory_Variance
 from tblMaterial with (nolock)
 where (((MaxQty) > 0))
 group by Vendor
 ) as Inventory on Inventory.Vendor = a.Vendor
group by 
    a.Vendor
  , GP
  , a.ThreeMonths
  , AllThreeMonths
  , CurrentInv
  , MaxInv
  , Inventory_Variance 
  /* Include the desired columns in the group by too */
  , ThisMonth.ARInvoiceID
  , ThisMonth.ARInvoiceDetailID
  , ThisMonth.ARInvoiceDate 
end

Reference:

Upvotes: 1

Related Questions