find the SQL Syntax error?

Question

Can anyone spot the syntax error in this sql?

SELECT * FROM ubuser 
INNER JOIN post ON (ubuser.usr_ID = post.pos_USERID) 
INNER JOIN ubFriendsLink ON (ubuser.usr_ID = (ub_lnkID1 OR ub_lnkID2)) 
WHERE"& session("ID") &" = (ub_lnkID1 OR ub_lnkID2) 
ORDER BY pos_DATE DESC

I cant spot anything wrong with it, but I am quite new to SQL.

this is the error:

Syntax error (missing operator) in query expression '(ubuser.usr_ID = post.pos_USERID) INNER JOIN ubFriendsLink ON (ubuser.usr_ID = (ub_lnkID1 OR ub_lnkID2)) WHERE18 = (ub_lnkID1 OR ub_lnkID2'.

thanks

UPDATE ------------------------------------

When I add a space to my sql as Dan suggested, I get this error

Microsoft Access Database Engine error '80040e14'

Syntax error (missing operator) in query expression '(ubuser.usr_ID = post.pos_USERID) INNER JOIN ubFriendsLink ON (ubuser.usr_ID = (ub_lnkID1 OR ub_lnkID2)'.

/S000000/newsfeed1.asp, line 28

and when I use the code juergen gave me, I get this:

Microsoft Access Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'ubuser.usr_ID = post.pos_USERIDINNER JOIN ubFriendsLink ON ubuser.usr_ID IN (ub_lnkID1,ub_lnkID2)WHERE 18 IN (ub_lnkID1,ub_lnkID2)ORDER BY pos_DATE DES'.

/S000000/newsfeed1.asp, line 31

thanks

Access doesn't support multiple join clauses, you have to 'fool' it into thinking that it's only joining two tables at a time: http://stackoverflow.com/questions/7854969/sql-multiple-join-statement — Yawar, Dec 21 '13 at 19:36

score 0 · Answer 1 · answered Dec 21 '13 at 13:58

0

SELECT * FROM ubuser 
INNER JOIN post ON ubuser.usr_ID = post.pos_USERID
INNER JOIN ubFriendsLink ON ubuser.usr_ID IN (ub_lnkID1,ub_lnkID2)
WHERE session("ID") IN (ub_lnkID1,ub_lnkID2) 
ORDER BY pos_DATE DESC

answered Dec 21 '13 at 13:58

juergen d

201,996
37
293
362

Don't you see in the error message where you missed to put spaces? It is kind of obvious. And at the end you wrote. `des` instead of `desc`. – juergen d Dec 21 '13 at 15:28
Ive put the spaces in and I did write desc. Still not working thought: `Syntax error (missing operator) in query expression 'ubuser.usr_ID = post.pos_USERID INNER JOIN ubFriendsLink ON ubuser.usr_ID IN (ubFriendsLink.ub_lnkID1,ubFriendsLink.ub_lnkID2) WHERE 18 IN (ubFriendsLink.ub_lnkID1,ubFriendsLink.ub_lnkID2) ORDER BY post.pos_DATE DES'.` – user3126012 Dec 21 '13 at 15:41
What is your complete query now? – juergen d Dec 21 '13 at 15:44
`"SELECT * FROM ubuser INNER JOIN post ON ubuser.usr_ID = post.pos_USERID INNER JOIN ubFriendsLink ON ubuser.usr_ID IN (ubFriendsLink.ub_lnkID1,ubFriendsLink.ub_lnkID2) WHERE "& session("ID") &" IN (ubFriendsLink.ub_lnkID1,ubFriendsLink.ub_lnkID2) ORDER BY post.pos_DATE DESC"` – user3126012 Dec 21 '13 at 15:45
Odd. The query seems fine. – juergen d Dec 21 '13 at 15:51
Thankyou, I was worried I was just terrible at this, ive tried running it through ms-access in sql design, changing the session("ID") for a valid user ID, and I get the same error, when I press help it says check punctuation and names are correct, which I have and they are correct. Ill send my tutor a email and see what he says, if we come up with a fix Ill let you know – user3126012 Dec 21 '13 at 16:01
If it's any consolation, I nominated the question for re-opening. Upon further review, look at the part starting with on ubuser.usr_id in (. You have unquoted strings in your list. That will cause an error. Same thing with where session("id") in (. If this comment is not clear, please say so and I'll edit my answer. – Dan Bracuk Dec 21 '13 at 16:27

score 0 · Answer 2 · answered Dec 21 '13 at 14:06

0

If this is part of your error message,

(ubuser.usr_ID = post.pos_USERID) 
INNER JOIN ubFriendsLink ON (ubuser.usr_ID = (ub_lnkID1 OR ub_lnkID2)) 
WHERE18 = (ub_lnkID1 OR ub_lnkID2'

You need a space here:

WHERE"& session("ID") &" = (ub_lnkID1 OR ub_lnkID2)

between WHERE and the quotation mark.

answered Dec 21 '13 at 14:06

Dan Bracuk

20,699
4
26
43

what do you mean details? – user3126012 Dec 21 '13 at 14:10
The term "didn't work" is vague. It could mean an error was thrown, it which case the details would be the error message. It could also mean unexpected results, in which case, the details would be the expected and actual results. In both cases, the code you used would be useful. I suggest editing your question where you post your attempt to follow both my an and @juergen's suggestions and the results of each attempt. – Dan Bracuk Dec 21 '13 at 15:12
ok thanks for the clarification, ill edit my question now o show you what I get when I add a space – user3126012 Dec 21 '13 at 15:19

find the SQL Syntax error?

2 Answers2