Hello all

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.

http://www.loghopper.com

__________________
http://www.loghopper.com

Hmm that should be fine...I wrote this in query analyzer and it parsed ok

SELECT
    f.id,
    f.objectID,
    f.object_type,
    b.id,
    b.userID,
    b.title,
    b.author,
    r.firstName,
    r.surName,
    r.email
FROM
    favs f
LEFT JOIN
    Books b
ON
   f.objectID = b.ID
LEFT JOIN
   restrictedAccess_db r
ON
   b.UserID = r.UserID
WHERE
   f.objectID =@value AND f.object_type = 'book'
ORDER BY
   b.title

"The one language all programmers understand is profanity."

Perhaps this line:
should be changed to this:
Woody Z http://www.learntoprogramnow.com

The type of join they use is relative to the data they are trying to retrive; I favor Outer Joins (in my example a Left Join) so that I can deal with any data that may be NULL.

"The one language all programmers understand is profanity."

Perhaps... and in this case, it is unclear that using a second outer join makes any sense, let alone the first one. Why bother bringing data back at all for the "favs" of a user who doesn't exist? Then you don't have to deal with the NULLS or anything else for that matter.

Woody Z http://www.learntoprogramnow.com

Again it is all strictly relative to what you are trying to do; there are instances where you need to do with NULL values, however, it is pointless to debate on this as we haven't a clue what the database looks like, values that should be returned, yadda yadda yadda. Good insight though woody.

"The one language all programmers understand is profanity."