SQL Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "XXX" could not be bound.

Bakersville
Bakersville used Ask the Experts™
on
Hi all

I wonder if anyone can help

I have a database in SQL, with 3 tables, broken down below

dbo.user
UserID, Int (AutoNumbered)
NumberOfQuestionsTagged, Int


Dbo.QuestionsAsked
QuesionID, Int (AutoNumber)
Deleted, Int (10 = No, 5 = Yes)


Dbo.TaggedQuestins
RecordID, Int (AutoNumber)
QuestionID, Int
UserID, Int

Now I have a view that displays the total number of questions tagged

Dbo.TotalTaggedQuestions

SELECT     dbo.Tags.UserID AS TagUserID, COUNT(dbo.Tags.RecordID) AS CountedTags, dbo. QuestionsAsked.Deleted
FROM         dbo.Tags INNER JOIN
                      dbo.user ON dbo.Tags.UserID = dbo.user.UserID INNER JOIN
                      dbo.Question ON dbo.Tags.QuestionID = dbo.QuestionsAsked.QuestionID
GROUP BY dbo.Tags.UserID, dbo.QuestionsAsked.Deleted
HAVING      (dbo.QuestionsAsked.Deleted = 10)

Now I am trying to use this information with a stored procedure.

Update dbo.user set NumberOfQuestionsTagged = dbo.TotalTaggedQuestions.CountedTags Where UserID = dbo.TotalTaggedQuestions.TagUserID

When ever I run this I get an error saying

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.TagsCounted.TagMemberID" could not be bound.

Can anyone tell me why I am getting this error and how I can get rid of it please

Any help would be greatly appreciated
Baker


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
>Update dbo.user set NumberOfQuestionsTagged = dbo.TotalTaggedQuestions.CountedTags Where UserID = dbo.TotalTaggedQuestions.TagUserID
the update must be a JOIN also. like this -...
Update u
  set NumberOfQuestionsTagged = ttq.CountedTags 
 from  dbo.Tags t
 JOIN dbo.user u ON t.UserID = u.UserID 
 JOIN dbo.Question q ON t.QuestionID = qa.QuestionID
 Where u.UserID = ttq.TagUserID

Open in new window

Awarded 2008
Awarded 2008
Commented:
try this:

Update u set NumberOfQuestionsTagged = dbo.TotalTaggedQuestions.CountedTags
from dbo.user u JOIN dbo.TotalTaggedQuestions ON u.UserID = dbo.TotalTaggedQuestions.TagUserID

Author

Commented:
Thank you for you quick reply.

I've just got another error now though..

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.user.UserID'.

Any ideas?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Just seen it's

JOIN dbo.user u ON t.UserID = u.UserID
 JOIN dbo.Question q ON t.QuestionID = qa.QuestionID

That is causing me the issue
u.UserID
qa.QuestionID
 
I have renamed them..  Just incase you were wondering..

Baker
Awarded 2008
Awarded 2008

Commented:
awesome...

Author

Commented:
Thank you both again, just got it to work, it needed another join.

Took a while to understand, but got there.

Thank you again for pointing me on the right track.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial