Double Inner Join generates unexpected error
- by Itamar Marom
In my database I have three tables:
Users: UserID (Auto Numbering), UserName, UserPassword and a few other unimportant fields.
PrivateMessages: MessageID (Auto Numbering), SenderID and a few other fields defining the message content.
MessageStatus: MessageID, ReceiverID, MessageWasRead (Boolean)
What I need is a query to which I input a user's id and I get all the private messages he has received. In addition, I also need to receive each message's sender 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 saving it in my Access database, I get the following error (translated to English by me, since my office is in a different language):
Syntax error (missing operator) at expression:
"PrivateMessages.SenderID = Users.UserID INNER JOIN MessageStatus ON
PrivateMessages.MessageID = MessageStatus.MessageI".
Any ideas what could cause this?
Thanks.
