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

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

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