What Is a 'Multi-Part Identifier' and Why Can't It Be Bound

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

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.

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 multipart identifier could not be bound in order/group by with joins

The multi-part identifier could not be bound it means the Compiler is not able to find the column specified.

Order By comes after where And If you use group by then you can only select those columns which as present in group by if you want to select other columns you have to use aggregate function.

SELECT a.[NR]
FROM [dbo].[A] a 
inner join [dbo].[NM] nm  on a.ID = nm.PropertyId 
inner join [dbo].[PRO] pro on pro.ID  = nm.PropertyID_FK
where  a.Set = 'Pl_ch'
GROUP BY a.[NR]
Order by a.[NR]

The multi-part identifier could not be bound even though everything is unique

Dmitrij Kultasev was spot on for the issue. Explicit joins happen first. So at the moment, the INNER JOIN is between e and b; a, c and d aren't in scope for that ON clause - hence the error (there's no a) and why it works when the commenting changes the join order (which now means you're joining a and b.

Fix your query to eliminate the old comma join syntax - it's from over a quarter of a century ago!

SELECT DISTINCT TOP 1000 
a.Name00,
a.UserName00,
a.Domain00,
a.TotalPhysicalMemory00,
a.Manufacturer00,
a.Model00,
a.MachineID,
a.SystemType00,
b.MACAddress00,
b.ServiceName00,
c.System_OU_Name0,
d.Name0,
e.Model00

FROM 
[dbo].[Computer_System_DATA] AS a
    INNER JOIN --?--
[dbo].[v_RA_System_SystemOUName] AS c
    ON
       --?-- What links a and c together?
    INNER JOIN  --?--
[dbo].[v_GS_PROCESSOR] AS d
    ON
       --?-- What links d to the combination of a and c?
    INNER JOIN --?--
[dbo].[Disk_DATA] AS e
    ON
       --?-- What links e to the combination of a, c and d?

INNER JOIN  [dbo].[Network_DATA] AS b ON  b.MachineID=a.MachineID
WHERE 
b.MACAddress00 IS NOT NULL AND b.ServiceName00 LIKE '%express'

Of course, you may want to switch around the order in which you perform the joins if e.g. the link between a and c is actually via d.

---

The multi-part identifier could not be bound even though everything is unique

The question title doesn't really make sense. An error stating "The multi-part identifier could not be bound" usually means that part of a name you've used somewhere isn't available at all at that location - not anything to do with multiple definitions. That would usually generate an error along the lines of "The correlation name '<x>' is specified multiple times".

(SQL Server Trigger) The multi-part identifier could not be bound

do it like this

SELECT @course_id = course_id, 
       @sec_id = sec, 
       @semester = semester,
       @year_ = year
  FROM INSERTED

then set your other values.

---

Related Topics