|
Subject:
|
Joining Three Tables
|
|
Posted By:
|
Alexpizzoferro
|
Post Date:
|
8/23/2006 10:28:16 AM
|
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
|
|
Reply By:
|
dparsons
|
Reply Date:
|
8/23/2006 12:29:50 PM
|
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."
|
|
Reply By:
|
woodyz
|
Reply Date:
|
8/31/2006 4:13:58 PM
|
Perhaps this line:
strSQL = strSQL & "JOIN restrictedAccess_db " should be changed to this:
strSQL = strSQL & "INNER JOIN restrictedAccess_db "
Woody Z http://www.learntoprogramnow.com
|
|
Reply By:
|
dparsons
|
Reply Date:
|
9/1/2006 7:40:18 AM
|
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."
|
|
Reply By:
|
woodyz
|
Reply Date:
|
9/1/2006 3:50:24 PM
|
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
|
|
Reply By:
|
dparsons
|
Reply Date:
|
9/1/2006 4:08:05 PM
|
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."
|
