join with subquery

I'm getting "incorrect syntax near keyword on" on the join to the contrstatuscodes table

      c.namesid as NamesId,
      c.contrid as ContrId,
    r.branchid as stat500

      from contracts c
      inner join names n
      on c.branchid = n.branchid
      and c.namesid = n.namesid

      left join ((select cs.branchid,
                       from contrstatuscodes cs
                       where cs.statuscode = '500') r
                        on r.branchid = n.branchid))  --Incorrect syntax near keyword 'on' here

                  payoutdate is not null
                  and payoutdate > '9/1/13'
            and stat500 is null
Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
For starters, the parentheses are not correct, and you can lose the alias cs as the subquery only has one table  
left join (select branchid, namesid from contrstatuscodes where statuscode = '500') r
   on r.branchid = n.branchid  --Incorrect syntax near keyword 'on' here

Open in new window

No points please.

2 resources you may find useful:
this is very precise in locating TSQL syntax erorrs (once you click 'format')
this will find fewer TSQL specific syntax errors, and is a bit more 'forgiving'
for example click this - for "small" queries it provides a url.

Using this I think you can spot the parentheses problem that Jim has sorted out for you.

hope you like'em. Cheers.
Delta7428Author Commented:
Awesome!  Great resources PortletPaul.

Points coming jimhorn
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
Delta7428Author Commented:
Thanks Jim
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.