title: How to retrieve a column from a nested sub-query in left join?
body: <p>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. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>In above example, I have to retrieve two columns from the left join <code>LEFT JOIN (SELECT vendor,tblarinv.arinv, tblardetail.arddate</code>. Originally, there is only vendor in the select statement but since I need <code>tblarinv.arinv, tblardetail.arddate</code>, 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? </p>
  
  <p>Here is my edited query:</p>
  
  <pre><code>SELECT 
  a.Vendor, 
  ,CASE WHEN sum(GP)&lt;&gt;0 THEN GP ELSE 0 END GP
  ,a.ThreeMonths
  ,CASE WHEN sum(GP)&lt;&gt;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&gt;=dateadd(m,-3,@First) 
      Group By Vendor
      Having sum(GrossProfit)&lt;&gt;0) a
  
  LEFT JOIN (Select tblMaterial.Vendor, tblARDetail.ARInvoiceID, tblARDetail.ARInvoiceDetailID, tblARInv.ARInvoiceDate
  ,(Sum(SplitAmount))-(Sum((CASE WHEN SplitAmount&lt;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&gt;= '' + 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
  </code></pre>
  
  <p>So, I need to retrieve <code>tblARDetail.ARInvoiceID, tblARDetail.ARInvoiceDetailID, tblARInv.ARInvoiceDate</code> which I added in above edited query.</p>