Click here to Skip to main content
14,640,918 members
Rate this:
Please Sign up or sign in to vote.
See more:
SELECT p.LastName, p.FirstName, p.Phone , pr.ProcDescription
FROM Patient p , Visit v , Visit_proc vp, [PROCEDURE] pr
WHERE p.MedicalRecNum = v.MedicalRecNum AND vp.VisitID = v.VisitID AND pr.ProcID = vp.ProcID 
AND pr.ProcDescription LIKE '%tomy'
ORDER BY pr.ProcDescription ASC, p.LastName ASC, p.FirstName DESC;


Msg 156, Level 15, State 1, Line 95
Incorrect syntax near the keyword 'AND'.


What I have tried:

I am new to this and unsure what to try.
Posted
Updated 5-Jun-20 11:57am
v2
Comments
Maciej Los 5-Jun-20 16:21pm
   
Why do you use WHERE statement to join tables? This is very old technic. You need to use Joins[^]!
User-12551084 7-Jun-20 13:50pm
   
you can use join instead of like this
WHERE p.MedicalRecNum = v.MedicalRecNum AND vp.VisitID = v.VisitID AND pr.ProcID = vp.ProcID
Rate this:
Please Sign up or sign in to vote.

Solution 1

Apart from the JOINs you should be using as Maciej has already mentioned, you need to look at the rest of your SQL code - the error message is specifically pointing you at "line 95" and there are only five in that entire fragment. It's quite likely that you are assuming where the error is incorrectly!
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Try this rewrite which is based on JOINs and see if the error persists
SELECT p.LastName, p.FirstName, p.Phone , pr.ProcDescription

FROM   Patient          p
INNER JOIN Visit        v ON p.MedicalRecNum = v.MedicalRecNum 
INNER JOIN Visit_proc  vp ON v.VisitID       = vp.VisitID
INNER JOIN [PROCEDURE] pr ON vp.ProcID       = pr.ProcID

WHERE    pr.ProcDescription LIKE '%tomy'
ORDER BY pr.ProcDescription ASC, p.LastName ASC, p.FirstName DESC;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100