SQL Server error: the multi-part identifier "C.Name" could not be bound

2
votes

I have this SQL query:

CREATE VIEW QueryV5 
AS
    SELECT DISTINCT 
        C.Name, P.Name, SUM(Duration.Time) AS TotalTime
    FROM 
        cmpt354_starwars.dbo.Characters C, 
        cmpt354_starwars.dbo.Planets P, 
        (SELECT (T.[Time of Departure] - T.[Time of Arrival]) AS Time 
         FROM cmpt354_starwars.dbo.TimeTable T 
         WHERE T.CharacterName = C.Name AND T.PlanetName = P.Name) AS Duration
    WHERE 
        P.Affiliation = 'neutral'

And I get the errors:

Msg 4104, Level 16, State 1, Procedure QueryV5, Line 3
The multi-part identifier "C.Name" could not be bound.

Msg 4104, Level 16, State 1, Procedure QueryV5, Line 3
The multi-part identifier "P.Name" could not be bound.

I don't understand what's going on, why it won't let me use Duration as an alias for the nested query. I've compared my query to other people's and I can't see any syntactic differences. What's going on?

2
sqlsql-server
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged - marc_s

2 Answers

2
votes

C.Name, P.Name both are having the same column name, that causing the issue. Please provide different alias name for any one of the column will solve the issue.

So this can be

SELECT DISTINCT C.Name, P.Name, SUM(Duration.Time) AS TotalTime

replace to this:

SELECT DISTINCT C.Name, P.Name AS PlantName, SUM(Duration.Time) AS TotalTime

UPDATE:

Could you try with the JOIN approach

CREATE VIEW QueryV5 AS 
SELECT DISTINCT 
    C.Name, P.Name AS PlanetName, SUM(T.[Time of Departure] - T.[Time of Arrival]) AS TotalTime 
FROM cmpt354_starwars.dbo.TimeTable T 
JOIN cmpt354_starwars.dbo.Characters C ON C.Name = T.CharacterName
JOIN cmpt354_starwars.dbo.Planets P ON P.Name = T.PlanetName
WHERE P.Affiliation = 'neutral' 
GROUP BY C.Name, P.Name
2
votes

Please, use ANSI syntax of JOIN:

CREATE VIEW QueryV5 
AS 

SELECT  T.CharacterName, 
        T.PlanetName, 
        SUM(T.[Time of Departure] - T.[Time of Arrival]) AS TotalTime 
FROM cmpt354_starwars.dbo.TimeTable T 
INNER JOIN cmpt354_starwars.dbo.Planets P
    ON T.PlanetName = P.Name
INNER JOIN cmpt354_starwars.dbo.Characters C
    ON T.CharacterName = C.Name 
WHERE P.Affiliation = 'neutral'
GROUP BY T.CharacterName, T.PlanetName

And there is no need for C.Name and P.Name - use columns from TimeTable.