Multi-part identifier could not be bound, without join

0
votes

Can anyone point out the problem with the below query, I keep getting the following error: "Msg 4104, Level 16, State 1, Line 1 The multi-part identifier could not be bound."

select top 1 * 
from [ADatabase].[dbo].[Tracking] 
where [ADatabase].[dbo].[Drivers].[DriverId] = [ADatabase].[dbo].[Tracking].[DriverOneId] 
and [UTCEventTime] > '2019-02-27' 
order by [Id] desc

I have looked at several examples of this issue but they all seem to be linked to more complex multi table join queries.

Thanks

2
sqlsql-servertsql
Tricky to tell - answers are saying you need join - but it may be that you were trying to do an existence check instead - what was your intention behind the [ADatabase].[dbo].[Drivers].[DriverId] = [ADatabase].[dbo].[Tracking].[DriverOneId] predicate? - Damien_The_Unbeliever
You can't use a table that doesn't appear in a FROM clause. Why is Drivers there? What are you trying to do? Return all matching rows from both tables? Or only Tracking fields that have a matching driver? - Panagiotis Kanavos

2 Answers

2
votes

You apparently need some sort of JOIN . . . And table aliases!

select top 1 t.*
from [ADatabase].[dbo].[Tracking] t join
     [ADatabase].[dbo].[Drivers] d
     on d.[DriverId] = t.[DriverOneId] and
        t.[UTCEventTime] > '2019-02-27'
order by t.[Id] desc;

You can only refer to tables and columns in tables that are in a FROM clause. You can't just add a four-part identifier in a WHERE or SELECT (or elsewhere) and expect SQL to understand it.

0
votes

Error message clearly says you need JOIN :

SELECT TOP (1) T.*, D.*
from [ADatabase].[dbo].[Tracking] T INNER JOIN
     [ADatabase].[dbo].[Drivers] D
     ON D.[DriverId] = T.[DriverId]
WHERE T.[UTCEventTime] > '2019-02-27' -- DON'T KNOW ABOUT TABLE WHERE IT COMES FROM
ORDER BY T.[Id] desc; -- DON'T KNOW ABOUT TABLE WHERE IT COMES FROM