Syntax error near JOIN in SQL Server database

Question

The following SQL query is giving below error when run against a Microsoft SQL Server database from C#:

Incorrect syntax near the keyword JOIN

Certainly, the query contains the columns mentioned in the SELECT portion.

SELECT
  Patient.FirstName,
  Patient.LastName,
  Patient.PatientId,
  Patient.PatientSer,
  CAST(Course.StartDateTime AS DateTime),
  Course.ClinicalStatus,
  CAST(Course.CompletedDateTime AS DateTime),
  CAST(ScheduledActivity.CreationDate AS DateTime),
  CAST(ScheduledActivity.ActualStartDate AS DateTime),
  CAST(ScheduledActivity.ActualEndDate AS DateTime),
  ScheduledActivity.ActivityNote,
  Activity.ActivityCode,
  Activity.ActivityType,
  Activity.ObjectStatus,
  CAST(Activity.HstryDateTime AS DateTime),
  Activity.HstryTaskName
FROM
  Patient
  INNER JOIN Course
    ON (Patient.PatientSer=Course.PatientSer)
  INNER JOIN ScheduledActivity
    ON (Patient.PatientSer=ScheduledActivity.PatientSer)
  INNER JOIN ActivityInstance
    ON (ScheduledActivity.ActivityInstanceSer=ActivityInstance.ActivityInstanceSer)
  INNER JOIN Activity
    ON (Activity.ActivitySer=ActivityInstance.ActivitySer)
WHERE
  Patient.PatientId=@patientId;

A very similar query is successfully parsed without any issues. I just can't for the life of me see, why this isn't working. For now, I would like someone to just make sure that there actually is a syntax error in this query as the system claims, because I sure can't spot it.

There might also be an issue with the C# script that a "colleague" is using to run the query against the database, but that is a whole different issue.

@Parfait The only thing that is mentioned in the documentation of the medical database system in question is that "SQL functions found in Microsoft SQL database are available". That is partly what makes this so frustrating. We have no idea which specific flavor of Microsoft SQL database this is. Its a proprietary system, so the only thing we can hope to do is a bit of reverse engineering. — sesodesa
– sesodesa, Commented Jul 30, 2020 at 17:25
Doesn't MS Access only allow one JOIN at the time, so the second JOIN must be in a subquery etc? — jarlh
– jarlh, Commented Jul 30, 2020 at 17:38
@Parfait The connection is made via the C# class System.Data.SqlClient.SqlConnection. The connection string given to the constructor of the class object is of the form "Server = <servername>; Database = <database>;Trusted_Connection = <bool>". — sesodesa
– sesodesa, Commented Jul 30, 2020 at 17:42
I can't find any syntax error in this statement, and neither can my SSMS. Are you sure the problem is in this sql statement and not a different one? — Zohar Peled
– Zohar Peled, Commented Jul 30, 2020 at 17:55
Consider posting the C# code block of this SQL string and tag this question with c# for those experts to help. You may have issues with spaces and line breaks in formatting query. — Parfait
– Parfait, Commented Jul 30, 2020 at 18:01

DRapp · Accepted Answer · 2020-08-02 16:26:08Z

1

I agree with tgolisch, getting table ALIAS name references both shortens the query from readability standpoint, but also in cases where you need the same table joined multiple times in the same query. Such as an employee ID and supervisor ID both point to a person table to get the name. Each ID represents a person. Having aliases can help.

Now, back to your original post. Does it actually work AS-WRITTEN under SSMS? when the patient ID is provided? If not, fix it there first. If the query DOES work, then its somewhere in how the query is prepared within your C# code. If so, post the entire code of C# (without actual login credential stuff), but the whole code to be assessed.

In its easiest test case, I would go in C# and try doing the query one step at a time such as getting EVERY column so you are not worrying about "CAST()" issues that may fail. ALSO, put a specific patient ID vs parameter just to test and make sure all the JOINs work. If you are failing on the join to begin with, it doesn't matter WHAT the other fields are. If the parameter is bad, that too has been removed from the equation of problem. Only other alias I would add is for the "ScheduledActivity" table reference to ex: "SA" or "SchA", and "ActivityInstance" to "AI" or "ActI"

        yourSqlCommand.CommandText =
@"SELECT 
          *
       FROM
          Patient P
             INNER JOIN Course C
                ON P.PatientSer = C.PatientSer
             INNER JOIN ScheduledActivity SA
                ON P.PatientSer = SA.PatientSer
                INNER JOIN ActivityInstance AI
                   ON SA.ActivityInstanceSer = AI.ActivityInstanceSer
                   INNER JOIN Activity A
                      ON AI.ActivitySer = A.ActivitySer
        WHERE
          P.PatientId=@parmForPatientID
";

Mino

Update to show how I personally write my SQL queries in C# using the leading "@" which allows one string to span multiple lines. I get the entire clarity of the SQL in 1 statement without guessing at where the concatenation on multiple lines comes in. I even changed the parameter to show the SQL "@"-based parameter name. I also try to prefix with "@parm" to prevent any ambiguity of coming from some table, or parameter... no guessing.

Reformatted query to show indentation of where tables rely on previous... IMO, easier to follow how A->B->C are related, also updated the aliases as noted in top post.

edited Aug 2, 2020 at 16:26

answered Jul 30, 2020 at 19:17

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sesodesa Over a year ago

We will have another go at this tomorrow. You brought up some good points that will be attempted.

DRapp Over a year ago

@SeSodesa, revised query minimally, but also shows indentation to see how tables relate to each other. I personally find left-table.left-column = right-table.right-column to not confuse where pieces are coming from too.

sesodesa Over a year ago

Turns out it was the C# string formatting in the end.

DRapp Over a year ago

@SeSodesa, although accepted, see my revised comment and C# style preparation of SQL commands.

tgolisch · Accepted Answer · 2020-07-30 17:45:47Z

0

SQL Server has several reserved words. It is really difficult to memorize them all, so it is a good idea to use delimiters around any table name which might (maybe) have the same name as a reserved word. Example: [delimited]

Try this:

SELECT
  P.FirstName,
  P.LastName,
  P.PatientId,
  P.PatientSer,
  CAST(C.StartDateTime AS DateTime) StartDateTime,
  C.ClinicalStatus,
  CAST(C.CompletedDateTime AS DateTime) CompletedDateTime,
  CAST(ScheduledActivity.CreationDate AS DateTime) CreationDate,
  CAST(ScheduledActivity.ActualStartDate AS DateTime) ActualStartDate,
  CAST(ScheduledActivity.ActualEndDate AS DateTime) ActualEndDate,
  ScheduledActivity.ActivityNote,
  A.ActivityCode,
  A.ActivityType,
  A.ObjectStatus,
  CAST(A.HstryDateTime AS DateTime) HstryDateTime,
  A.HstryTaskName
FROM
  [Patient] P
  INNER JOIN [Course] C
    ON (P.PatientSer=C.PatientSer)
  INNER JOIN ScheduledActivity
    ON (P.PatientSer=ScheduledActivity.PatientSer)
  INNER JOIN ActivityInstance
    ON (ScheduledActivity.ActivityInstanceSer=ActivityInstance.ActivityInstanceSer)
  INNER JOIN [Activity] A
    ON (A.ActivitySer=ActivityInstance.ActivitySer)
WHERE
  P.PatientId=@patientId;

answered Jul 30, 2020 at 17:45

tgolisch

6,7743 gold badges27 silver badges46 bronze badges

1 Comment

jarlh Over a year ago

I can't find any reserved table name there. See en.wikipedia.org/wiki/SQL_reserved_words.

Collectives™ on Stack Overflow

Syntax error near JOIN in SQL Server database

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related