Forums
This topic is locked
Inner join multiple tables
Posted 17 Feb 2012 18:41:54
1
has voted
17 Feb 2012 18:41:54 Dave Desormeaux posted:
I looked for a solution but couldn't find one in the forums (please excuse me if I'm being repititious here...)I'm trying to join 3 (and if I get this to work, possibly more) tables to display the combined data on an ASP page. I'm stumped.
This works:
SELECT * FROM interviews
INNER JOIN releases ON releases.release_ID = interviews.release_id
and this works:
SELECT * FROM interviews
INNER JOIN spokesperson ON spokesperson.spk_ID = interviews.spk_id
But this doesn't work:
SELECT * FROM interviews
INNER JOIN releases ON releases.release_ID = interviews.release_id
INNER JOIN spokesperson ON spokesperson.spk_ID = interviews.spk_id
I get an ODBC error message: Sytnax error in FROM clause.
Any ideas would be very much appreciated.
DD
Replies
Replied 17 Feb 2012 20:08:59
17 Feb 2012 20:08:59 Dave Desormeaux replied:
OK, this always happens - I struggle for days and within hours of posting on a forum I find the answer. So for others who might have the same issue, here's the solution I found.
BTW, I'm using Dreamweaver CS3 (using recordsets to test the SQL), ASP, and ACCESS 2007.
Apparently, it's all in the parentheses - you need to add a set of parentheses for each table you are joining:
Join 2 tables:
SELECT *
FROM interviews INNER JOIN releases
ON releases.release_ID=interviews.release_id
Join 3 tables
SELECT *
FROM (interviews INNER JOIN releases ON releases.release_ID = interviews.release_id)
INNER JOIN spokesperson ON spokesperson.spk_ID = interviews.spk_id
WHERE interviews.spk_id = 1
Join 4 tables
SELECT *
FROM (((interviews INNER JOIN releases ON releases.release_ID = interviews.release_id)
INNER JOIN spokesperson ON spokesperson.spk_ID = interviews.spk_id)
INNER JOIN Current ON Current.ID = interviews.current_id)
Hope this makes sense and helps other newbies out there!!
DD
BTW, I'm using Dreamweaver CS3 (using recordsets to test the SQL), ASP, and ACCESS 2007.
Apparently, it's all in the parentheses - you need to add a set of parentheses for each table you are joining:
Join 2 tables:
SELECT *
FROM interviews INNER JOIN releases
ON releases.release_ID=interviews.release_id
Join 3 tables
SELECT *
FROM (interviews INNER JOIN releases ON releases.release_ID = interviews.release_id)
INNER JOIN spokesperson ON spokesperson.spk_ID = interviews.spk_id
WHERE interviews.spk_id = 1
Join 4 tables
SELECT *
FROM (((interviews INNER JOIN releases ON releases.release_ID = interviews.release_id)
INNER JOIN spokesperson ON spokesperson.spk_ID = interviews.spk_id)
INNER JOIN Current ON Current.ID = interviews.current_id)
Hope this makes sense and helps other newbies out there!!
DD