ms access / sql server syntax (i guess)

Question

this query:

SELECT sentmails.[VersandDatum], 
       sentmails.[DatumMailGeplant], 
       mailintervals.[Interval], 
       definitions.[Subject], 
       users.[Name], 
       sentmails.[MailArt], 
       Objekte.[Name], 
       sentmails.[Erledigt], 
       sentmails.[ID]
  FROM Objekte 
RIGHT JOIN (users RIGHT JOIN (definitions RIGHT JOIN (mailintervals RIGHT JOIN (sentmails) ON mailintervals.ID=sentmails.Intervall) ON definitions.ID=sentmails.Noti_ID) ON users.UID=sentmails.Funktion) ON Objekte.OId=sentmails.Objekt_ID

...works fine in ms access qbe, but in sql server it returns a "incorrect syntax near ')'" error. I'm really wondering why:

the joins need to be in brackets?
no more outer joins in sql server?
somewhere more brackets needed?

Mitch Wheat · Accepted Answer · 2010-10-10 02:00:07Z

1

MS Access has a slightly different JOIN syntax. Use this instead:

SELECT 
    sentmails.[VersandDatum], 
    sentmails.[DatumMailGeplant], 
    mailintervals.[Interval], 
    definitions.[Subject], 
    users.[Name], 
    sentmails.[MailArt], 
    Objekte.[Name], 
    sentmails.[Erledigt], 
    sentmails.[ID]
FROM Objekte 
RIGHT JOIN sentmails ON Objekte.OId=sentmails.Objekt_ID
RIGHT JOIN definitions definitions.ID=sentmails.Noti_ID
RIGHT JOIN mailintervals ON mailintervals.ID=sentmails.Intervall
RIGHT JOIN users ON users.UID=sentmails.Funktion

answered Oct 10, 2010 at 2:00

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

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

1 Comment

Stefan Over a year ago

just trying sqlstr.Replace("RIGHT JOIN", "RIGHT OUTER JOIN").Replace("(", "").Replace(")", "") and it seems to be getter better

Michael Petrotta · Accepted Answer · 2010-10-10 02:02:50Z

1

Your JOIN syntax is incorrect. You should be joining each table individually, without nested parentheses. You probably want something like:

SELECT sentmails.[VersandDatum]
    , sentmails.[DatumMailGeplant]
    , mailintervals.[Interval]
    , definitions.[Subject]
    , users.[Name]
    , sentmails.[MailArt]
    , Objekte.[Name]
    , sentmails.[Erledigt]
    , sentmails.[ID]
FROM Objekte
RIGHT JOIN sentmails ON Objekte.OId = sentmails.Objekt_ID
RIGHT JOIN users ON users.UID = sentmails.Funktion
RIGHT JOIN definitions ON definitions.ID = sentmails.Noti_ID
RIGHT JOIN mailintervals ON mailintervals.ID = sentmails.Intervall

Here's a starting point in MSDN.

edited Oct 10, 2010 at 2:02

answered Oct 10, 2010 at 1:57

Michael Petrotta

61.1k27 gold badges153 silver badges181 bronze badges

Collectives™ on Stack Overflow

ms access / sql server syntax (i guess)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related