I am trying to write an SQL statement using INNER JOIN to retrieve data from 3 different tables.
This is what I have:
SELECT * FROM tbl_WhatsOn INNER JOIN tbl_Theatres ON tbl_WhatsOn.WhatsOnVenue = tbl_Theatres.TheatresID WHERE WhatsOnCity = " + Replace(rs_WhatsOnByCity__MMColParam, "'", "''" + " ORDER BY Date ASC
It works great, but I need to reference two other tables to display the foreign key fields from tbl_WhatsOn, namely tbl_City and tbl_Province.
Any suggestions? I am using Dreamweaver MX and Access db.

Edited by - sunnyside on 27 Jul 2005 15:57:56

try this:
city = Replace(rs_WhatsOnByCity__MMColParam, "'", "''"
SQL = "select * from tbl_WhatsOn, tbl_city, tbl_province where WhatsOnVenue = TheatresID and WhatsOnProvince = ProvinceID and WhatsOnCity = CityID and whatsOnCity = '" & city & "'"

That should do the trick.