Clever Geek Handbook

Double Inner Join generates unexpected error

There are three tables in my database:

  • Users: UserID (auto-numbering), UserName, UserPassword and a few other unimportant fields.
  • PrivateMessages: MessageID (auto-numbering), SenderID, and a few other fields that define the content of the message.
  • MessageStatus: MessageID, ReceiverID, MessageWasRead (Boolean)

I need a request to which I enter the user id and get all the private messages he received. In addition, I also need to get each message from the sender's UserName. For this, I wrote the following query:

SELECT Users.*, PrivateMessages.*, MessageStatus.*
FROM PrivateMessages
INNER JOIN Users ON PrivateMessages.SenderID = Users.UserID
INNER JOIN MessageStatus ON PrivateMessages.MessageID = MessageStatus.MessageID
WHERE MessageStatus.ReceiverID=[@userid];

But for some reason, when I try to save it to my Access database, I get the following error (English translation by me since my office is in a different language):

Syntax error (missing operator) in expression: "PrivateMessages.SenderID = Users.UserID INNER JOIN MessageStatus ON PrivateMessages.MessageID = MessageStatus.MessageI".

Any ideas what might be causing this? Thank.

+3


source to share


1 answer


You need parentheses with MS Access:



SELECT Users.*, PrivateMessages.*, MessageStatus.*
FROM (PrivateMessages
INNER JOIN Users ON PrivateMessages.SenderID = Users.UserID)
INNER JOIN MessageStatus ON PrivateMessages.MessageID = MessageStatus.MessageID
WHERE MessageStatus.ReceiverID=[@userid];
+4


source






More articles:


All Articles