From having an ID number of a field in a favs table, which is the same as that in the books table, I need to retrieve information about the book. I can get this to work but I also need to join to a third table, the user table by using the userID in the books table and joining to the user table to then retrieve informaiton about that user.
I hope this makes sense.
The code I have written is below, it should be evident from this what I want to do.
strSQL = "SELECT favs.ID, favs.objectID, favs.object_type, books.ID, books.userID, books.title, books.author, restrictedAccess_db.first_name, restrictedAccess_db.surname, restrictedAccess_db.email "
strSQL = strSQL & "FROM favs "
strSQL = strSQL & "LEFT JOIN books "
strSQL = strSQL & "ON favs.objectID = books.ID "
strSQL = strSQL & "JOIN restrictedAccess_db "
strSQL = strSQL & "ON books.userID = restrictedAccess_db.userID "
strSQL = strSQL & "WHERE favs.objectID = '" & u_ID & "' And favs.object_type = 'book' "
strSQL = strSQL & "ORDER BY books.title;"
This gives me the following error:
Syntax error (missing operator) in query expression 'favs.objectID = books.ID JOIN restrictedAccess_db ON books.userID = restrictedAccess_db.userID'.
I hope someone can help.
Thanks in advance.