Forums

ASP

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

Reply to this topic