CODEWITHSUNDEEP
sql
pmillio
pmillio

Reputation: 1139

The multi-part identifier "alias.field" could not be bound

Hi i keep getting the error "The multi-part identifier "Users.USERS_ID" could not be bound" i am unsure of what is wrong with the query.

This is what my query looks like;

SELECT Events.*, Users.UserName,Users.USERS_ID, 
Users.ThumbnailPic,
Users.CountryName,
ISNULL(UserStatusUpdates.UserStatus,'No Updates')AS LastUpdate,
UserStatusUpdates.MediaTypeID,UserStatusUpdates.USERSTATUS_ID,(UserStatusUpdates.AddDate)

FROM Events
JOIN Users ON Events.UserID = Users.USERS_ID
LEFT  JOIN (SELECT TOP 1 UserStatusUpdates.UserStatus,UserStatusUpdates.MediaTypeID,
UserStatusUpdates.USERSTATUS_ID,UserStatusUpdates.AddDate, UserStatusUpdates.UserID
  FROM UserStatusUpdates  where UserStatusUpdates.UserID = Users.USERS_ID
ORDER BY AddDate DESC) AS UserStatusUpdates ON UserStatusUpdates.UserID = Events.UserID 

WHERE Events.EventID = @EventID 
AND Users.bDeleted = 'False' 
AND Users.bSuspended =  'False'
END

Can anyone see what I am doing wrong

Upvotes: 0

Views: 1396

Answers (1)

hatchet - done with SOverflow
hatchet - done with SOverflow

Reputation: 16259

I'm pretty sure the problem is in this bit:

LEFT  JOIN (SELECT TOP 1 UserStatusUpdates.UserStatus,UserStatusUpdates.MediaTypeID,
UserStatusUpdates.USERSTATUS_ID,UserStatusUpdates.AddDate, UserStatusUpdates.UserID
  FROM UserStatusUpdates  where UserStatusUpdates.UserID = Users.USERS_ID
ORDER BY AddDate DESC) AS UserStatusUpdates ON UserStatusUpdates.UserID = Events.UserID

The SELECT TOP 1 clause does not have access to Users.USERS_ID.

I don't see where you are using any of the data returned from UserStatusUpdates. So I'm assuming you are using that bit to filter out rows where that join produces nothing. In that case, you may be able to move that out of a join, and into the where with an EXISTS. But it's a left join, so even my assumption doesn't really apply.

I think the solution is described in the question and answer Top 1 with a left join

Upvotes: 1

Related Questions