user2469321 user2469321 - 6 months ago 16
SQL Question

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

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?

Answer

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