Complex SQL Statement

I am using Access97 and have a db with 7 tables from which I need to draw data. I either get an error or a recset with hundreds of repeated rows (Even using DISTINCT). I can't seem to get the INNER JOIN to work right. From the main table I need to look up 0 or more rows in related tables. The syntax of INNER JOIN is killing me. A typical attempt looks like this:

SELECT EventDate, EventName, PlantName, RegionName, CurrentPrice
FROM (Regions INNER JOIN CurrentPrices ON Regions.RegionID = CurrentPrices.RegionID) INNER JOIN (PlantName INNER JOIN (EventType INNER JOIN Events ON EventType.EventID = Events.EventID) ON PlantName.PlantID = Events.PlantID) ON Regions.RegionID = PlantName.RegionID;

Can someone help me sort out the proper syntax?

Thanks,

:Scott

Hmm, maybe you should try the RIGHT and/or LEFT JOIN. If you're using Access, then you can define different join types, when you double click on the join line (in the query wizard win or smht like that).

With regards,

You cant look up zero or more rows. Inner join is used for returning records from tables where the joined tables have common data. If there is the possibility that one of the tables has no data that matches that criteria, then you will get no records at all for that match.

I assume that you have used the query builder in Access to build the query in the first place, therefore the query should be syntactically correct, so that means you conditions are wrong.

BTW, DISTINCT doesn't stop it from returning multiple rows, just multiple rows with the same data. If you only want the first row, then you could try SELECT TOP 1 FROM .....

Hope I got that syntax right. That will make it return only one record.