The Multi-Part Identifier Could Not Be Bound

The multi-part identifier could not be bound

You are mixing implicit joins with explicit joins. That is allowed, but you need to be aware of how to do that properly.

The thing is, explicit joins (the ones that are implemented using the JOIN keyword) take precedence over implicit ones (the 'comma' joins, where the join condition is specified in the WHERE clause).

Here's an outline of your query:

SELECT
  …
FROM a, b LEFT JOIN dkcd ON …
WHERE …

You are probably expecting it to behave like this:

SELECT
  …
FROM (a, b) LEFT JOIN dkcd ON …
WHERE …

that is, the combination of tables a and b is joined with the table dkcd. In fact, what's happening is

SELECT
  …
FROM a, (b LEFT JOIN dkcd ON …)
WHERE …

that is, as you may already have understood, dkcd is joined specifically against b and only b, then the result of the join is combined with a and filtered further with the WHERE clause. In this case, any reference to a in the ON clause is invalid, a is unknown at that point. That is why you are getting the error message.

If I were you, I would probably try to rewrite this query, and one possible solution might be:

SELECT DISTINCT
  a.maxa,
  b.mahuyen,
  a.tenxa,
  b.tenhuyen,
  ISNULL(dkcd.tong, 0) AS tongdkcd
FROM phuongxa a
  INNER JOIN quanhuyen b ON LEFT(a.maxa, 2) = b.mahuyen
  LEFT OUTER JOIN (
    SELECT
      maxa,
      COUNT(*) AS tong
    FROM khaosat
    WHERE CONVERT(datetime, ngaylap, 103) BETWEEN 'Sep 1 2011' AND 'Sep 5 2011'
    GROUP BY maxa
  ) AS dkcd ON dkcd.maxa = a.maxa
WHERE a.maxa <> '99'
ORDER BY a.maxa

Here the tables a and b are joined first, then the result is joined to dkcd. Basically, this is the same query as yours, only using a different syntax for one of the joins, which makes a great difference: the reference a.maxa in the dkcd's join condition is now absolutely valid.

As @Aaron Bertrand has correctly noted, you should probably qualify maxa with a specific alias, probably a, in the ORDER BY clause.

What is a 'multi-part identifier' and why can't it be bound?

A multipart identifier is any description of a field or table that contains multiple parts - for instance MyTable.SomeRow - if it can't be bound that means there's something wrong with it - either you've got a simple typo, or a confusion between table and column. It can also be caused by using reserved words in your table or field names and not surrounding them with [].
It can also be caused by not including all of the required columns in the target table.

Something like redgate sql prompt is brilliant for avoiding having to manually type these (it even auto-completes joins based on foreign keys), but isn't free. SQL server 2008 supports intellisense out of the box, although it isn't quite as complete as the redgate version.

The multi-part identifier could not be bound WITH clause

CTEs aren't automatically included in the attached query, you have to mention them in a FROM or JOIN clause.

Your current query is of the form:

WITH
   CTE AS ( ... ),
   totAlarms  AS ( ... )
SELECT ...
FROM CTE
ORDER BY ...

So only CTE is visible in the SELECT clause. totAlarms is defined but never used - like creating a VIEW but not writing any queries that reference it.

Since your two CTEs are independent, you need to CROSS JOIN them, giving this form:

WITH
   CTE AS ( ... ),
   totAlarms  AS ( ... )
SELECT ...
FROM CTE
CROSS JOIN totAlarms
ORDER BY ...

Or using the , operator, which is effectively a cross join:

WITH
   CTE AS ( ... ),
   totAlarms  AS ( ... )
SELECT ...
FROM CTE, totAlarms
ORDER BY ...

The multi-part identifier could not be bound in SQL Server and it confused

Don't mix implicit and explicit joins! Matter of fact, don't use implicit joins: this is archaic syntax, that should not appear in new code.

The comma in the FROM clause should (probably) be a CROSS JOIN:

SELECT stu.sno, sname, cname
FROM sc scc
CROSS JOIN (SELECT AVG(sc.grade) AS avg_grade FROM sc GROUP BY sc.cno) AS avg_grades
INNER JOIN course c on c.cno = scc.cno
INNER JOIN s stu on stu.sno = scc.sno;

Note that, for this subquery to be useful, you would probably need to select column avg_grade. I would also recommend prefixing each column with the table it belongs to, to remove any possible ambiguity.

Finally: you (probably) can use window functions instead of a subquery:

SELECT stu.sno, sname, cname, scc.
FROM (SELECT *, AVG(grade) OVER() avg_grade FROM sc) scc
INNER JOIN course c on c.cno = scc.cno
INNER JOIN s stu on stu.sno = scc.sno;

The multi-part identifier could not be bound for update statement from 'inner' set to 'update' set

this happened because on where ar.[Id] = ar_update.[Id] because ar_update.[Id] is not a value it is a multivalued type, it's like you were comparing a list with an integer, try this

update ar_update 
   set ar_update.[GLEntry_Amount] = ar_sum.[GLEntry_Amount_sum]
  from [dbo].[ASL_Result_VGO]  as ar_update  

  join (select  ar.[Id]                        as [Id]
               ,ar.[Company Id]                as [Company Id]
               ,ar.[Schedule Name]             as [Schedule Name]       
               ,ar.[Line No_]                  as [Line No_]            
               ,ar.[Row No_]                   as [Row No_]             
               ,ar.[Posting Date]              as [Posting Date]        
               ,ar.[Business Centre Code]      as [Business Centre Code]
               ,ar.[Site Locations Code]       as [Site Locations Code] 
               ,GLEntry_Amount_sum =     (select  sum(are.[GLEntry_Amount])     
                     from    [dbo].[ASL_Result_VGO]  as are
                     where   are.[Company Id]           = ar.[Company Id]  and          
                             are.[Schedule Name]        = ar.[Schedule Name]  and       
                             are.[Posting Date]         = ar.[Posting Date]  and          
                             are.[Business Centre Code] = ar.[Business Centre Code]  and   
                             are.[Site Locations Code]  = ar.[Site Locations Code]  and 
                             are.[Row No_] in ( select * from dbo.udf_range_expand_VGO(ar.[Totaling]) )  
                    )

        from    [dbo].[ASL_Result_VGO]  as ar
 

       )  ar_sum  on  ar_update.[Id] = ar_sum.[Id]           

    where   ar_update.[Totaling Type] = 2  and   -- formulas
            len(trim(ar_update.[Totaling])) > 0  and      
            charindex('..', ar_update.[Totaling]) > 0  

SQL Server Subquery Error - The Multi-Part Identifier Could Not Be Bound

a Is not available at the top level of your select; it was lost at the moment b was created. If you want to reference columns in a, the subquery b would have to select them and then you call them b.whatever. Because you're grouping you might not realistically be able do this because when you select them at the level of b you'll either have to add them to the group by (in which case they will cause subdivisions of the group) or add them as an aggregate (in which case they will become mixed with other row values an no longer represent) , so you'll have to join the table again at the outer level.

I'm not really sure what the double grouping you have is achieving because it groups and sums then filters and groups and sums again. If you had this:

A,B,C
X,Y,2
X,Y,3
X,Z,4
A,B,1

And you group by A,B, summed C then where only X then group by just A and summed again it would go from:

--group by A B
A,B,C
X,Y,5
X,Z,4
A,B,1

To:

--where x
A,B,C
X,Y,5
X,Z,4

To:

--group by a   
A,C
X,9

But you might as well have just WHERE X GROUP BY A SUM C - the first groupby didn't give you anything

The key point here is that, aside from not being able to use columns at an outer level if they weren't selected at an inner level, once you've lost detail in a grouping operation, you can't get it back (it's like huge lossy compression of an image; once degraded it will always look blocky and pixelated no matter what the movies "I need that grey square of a license plate enhanced and readable" tell you); you have to go back to the source data with the detail and join your grouped data onto it via whatever columns you have left as a group

As such I think you can rewrite your query like this:

SELECT 
    *
 FROM
    AWS_Stage a
    INNER JOIN
     (SELECT 
          yr,
          part_number,
          SUM(a.gross) AS sumgross2019
      FROM
          AWS_Stage
      WHERE  
          yr = 2019
      GROUP BY 
          yr, part_number
      ) x
     ON a.yr = x.yr AND a.part_number = x.part_number

But it's kinda hard to know whether this is what you want from "I tried this non working thing" and "top 50 sku" (why is 2019 in the query when the spec didn't say "from last year" etc).

If you're looking for "all of the 2019 detail plus the sum of all the gross per partnumber for 2019" this is it (except for the top 50 part, which I'm sure you can add). It can also be written using a window function ..

SELECT 
    *,
    SUM(gross) OVER(PARTITION BY a.part_number) as sumgrossperpartnumber
FROM
    AWS_Stage a
WHERE 
    a.yr = 2019

If you're looking for multi level or multi factor grouping, the window function way there might be simpler. If you want to compare years, move yr out of the where clause and add it into the partition by instead.
If you wanted all the detail from all years, but only grossing up from last year you can:

SELECT 
    *,
    SUM(CASE WHEN yr = 2019 THEN gross ELSE 0 END) OVER(PARTITION BY a.part_number) as sumgrossperpartnumber
FROM
    AWS_Stage a

---

Related Topics