Multi-Part Identifier Missing

Experts,

The attached stored proc is getting an error on t.MemberGUID:

"The multi-part identifier 't.MemberGUID" could not be found."

The last time I has this error, it was becasue I was referencing a field without referencing the table in the FROM clause.  This is clearly not the case here.  Ideas?
SELECT Member.MemberID, '01/01/1900' as servicedate
, 'HRA-Triglyceride' as loincCode,null as cpt_Code
, a.hramadAnswer as llc_Value 
FROM HRAMemAnswerData a WITH(NOLOCK), PopulationTask t, HRAGroupQuestions gq, HRAQuestion q  
INNER JOIN Member ON Member.MemberGUID=t.MemberGUID 
where a.PopulationTaskID = t.PopulationTaskID And a.HragqID = gq.HragqID 
And gq.HragqChildqID = q.HraqID and hraqid = 19 and Member.MemberID = @MemberID 
AND T.Active = 0 
AND LEFT(a.hramadAnswer, 3) = 'Yes' AND LTRIM(RTRIM(SUBSTRING(a.hramadAnswer, 5, LEN(a.hramadAnswer)))) != ''  and Member.ClientID = @ClientID  and Member.MemberID = @MemberID ORDER BY ServiceDate DESC

Open in new window

LVL 1
NigelRocksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
do not mix join and non join syntax...

SELECT Member.MemberID, '01/01/1900' as servicedate
, 'HRA-Triglyceride' as loincCode,null as cpt_Code
, a.hramadAnswer as llc_Value 
FROM HRAMemAnswerData a WITH(NOLOCK)
JOIN PopulationTask t
  ON a.PopulationTaskID = t.PopulationTaskID 
 AND T.Active = 0 
JOIN HRAGroupQuestions gq
  ON a.HragqID = gq.HragqID 
JOIN HRAQuestion q  
  ON gq.HragqChildqID = q.HraqID 
 AND q.hraqid = 19 
JOIN Member m 
  ON m.MemberGUID=t.MemberGUID 
 AND m.MemberID = @MemberID 
 AND m.ClientID = @ClientID  
WHERE LEFT(a.hramadAnswer, 3) = 'Yes' 
  AND LTRIM(RTRIM(SUBSTRING(a.hramadAnswer, 5, LEN(a.hramadAnswer)))) != ''  
ORDER BY ServiceDate DESC

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeTooleCommented:
Silly question, but: is there a field called MemberGUID in the table PopulationTask? - MemberID is used elsewhere in the query
0
Göran AnderssonCommented:
I cleaned up the query a bit to see what it was doing. (Making inner joins out of classical joins, removing duplicate MemberID conditions...) You haven't specified which table the hraqid and ServiceDate fields come from., but other than that it's pretty clear where everything comes from.

Check that you actually have a MemberGUID field in the PopulationTask table, and that you have spelled it correctly.
select
   m.MemberID,
   '01/01/1900' as servicedate,
   'HRA-Triglyceride' as loincCode,
   null as cpt_Code,
   a.hramadAnswer as llc_Value 
from
   HRAMemAnswerData a WITH(NOLOCK),
   inner join PopulationTask t on t.PopulationTaskID = a.PopulationTaskID
   inner join HRAGroupQuestions gq on gq.HragqID = a.HragqID
   inner join HRAQuestion q on q.HraqID = gq.HragqChildqID
   inner join Member m ON m.MemberGUID = t.MemberGUID
where
   hraqid = 19 and
   t.Active = 0 and
   left(a.hramadAnswer, 3) = 'Yes' and
   ltrim(rtrim(substring(a.hramadAnswer, 5, len(a.hramadAnswer)))) != '' and
   m.ClientID = @ClientID and
   m.MemberID = @MemberID
order by
   ServiceDate desc

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.