Search code examples
sqlsql-serveridentifier

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

So I'm getting an IntelliSense error and I can't figure out why. I've renamed everything to use aliases and I've ready everything I can on mutli-part identifiers and it seems to suggest that it's not unique? But with an Alias it seems to be unique, although "MachineID" is referenced in a number of tables

Here's my query 

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,
[dbo].[v_RA_System_SystemOUName] AS c,
[dbo].[v_GS_PROCESSOR] AS d,
[dbo].[Disk_DATA] AS e

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

The error is showing on line 22 at a.MachineID

What am I missing? Also, the error goes away if I comment out the following;

--c.System_OU_Name0,
--d.Name0,
--e.Model00


--[dbo].[v_RA_System_SystemOUName] AS c,
--[dbo].[v_GS_PROCESSOR] AS d,
--[dbo].[Disk_DATA] AS e

Any help is massively appreciated!

Solution

  • 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".