CODEWITHSUNDEEP
sql-server
Jeff
Jeff

Reputation: 21

Incorrect syntax near the keyword 'group' trying to link multiple tables in SQL Server

I am trying to link several tables together in SQL Server. The code below shows how I have linked the tables together so far:

select *
from profile
left join learner l on l.learnerid = profileid
left join learner_levels ll on ll.learnerid = l.learnerid
left join subjects s on s.subjectid = ll.subjectid
left join learner_group lg on lg.learnerid = profileid
where ll.archived = '0' and ll.completed = '0'
order by surname asc`

What i want to do is filter the results by "groupid" which is in the table "group". if I add that as a 'left join' statement I get the error stated in the title - "Incorrect syntax near the keyword 'group'. "

This is what I tried:

select *
from profile
left join learner l on l.learnerid = profileid
left join learner_levels ll on ll.learnerid = l.learnerid
left join subjects s on s.subjectid = ll.subjectid
left join learner_group lg on lg.learnerid = profileid
left join group g on g.groupid = lg.learnerid
where ll.archived = '0' and ll.completed = '0' and g.group_name = 'class 1'
order by surname asc`

This is the result in SQL Server Management Studio:

Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'group'.

Where am I going wrong?

Upvotes: 2

Views: 2233

Answers (5)

James Curran
James Curran

Reputation: 103495

"group" is a keyword, so you'll have to write that line as :

left join [group] g on g.groupid = lg.learnerid

Upvotes: 0

FlyingStreudel
FlyingStreudel

Reputation: 4464

Use [group] to specify the table vs the reserved word group

Upvotes: 0

devio
devio

Reputation: 37205

If you use a reserved word, such as GROUP, as a table name, you need to quote it using square brackets:

LEFT JOIN [Group] g

Upvotes: 1

Mike M.
Mike M.

Reputation: 12511

Wrap Group in []'s

Group is a reserved word, as Tobias said. They need to be changed or wrapped in square-brackets.

select * from profile left join learner l on l.learnerid = profileid left join learner_levels ll on ll.learnerid = l.learnerid left join subjects s on s.subjectid = ll.subjectid left join learner_group lg on lg.learnerid = profileid left join [group] g on g.groupid = lg.learnerid where ll.archived = '0' and ll.completed = '0' and g.group_name = 'class 1' order by surname asc

Upvotes: 2

Tobiasopdenbrouw
Tobiasopdenbrouw

Reputation: 14039

Group is a reserved word. Try to use a different name (for the table). Or put square brackets around [group].

Upvotes: 5

Related Questions