Mysterious MapInfo query syntax errors

Question

I'm struggling to get what seems like a perfectly normal query to execute in MapInfo v10.

In SQLite Manager (an excellent firefox plugin), this works as expected:

SELECT area_name, street_name, address, disp_lon, disp_lat
FROM mtdArea, pointAddress, streets
WHERE (streets.link_ID = pointAddress.link_ID) AND ((l_area_ID = area_ID) OR (r_area_ID = area_ID))

The equivalent in MapInfo

SELECT RationalisedMtdArea.Area_Name, RationalisedStreets.Street_Name, RationalisedPointAddress.address, RationalisedPointAddress.Disp_Lon, RationalisedPointAddress.Disp_Lat
FROM RationalisedMtdArea, RationalisedPointAddress, RationalisedStreets
WHERE RationalisedStreets.Link_ID = RationalisedPointAddress.Link_ID And ((RationalisedMtdArea.Area_ID = RationalisedStreets.L_Area_ID) Or (RationalisedMtdArea.Area_ID = RationalisedStreets.R_Area_ID))

..gives me a

No Join specified between tables RationalisedMtdArea and RationalisedPointAddress.
Invalid join condition in WHERE clause.

Does MapInfo not like logical Ors?
What am I missing?
Thanks.

Answer 1

As Mr Chimp says, MapInfo doesn't like more complex joins in queries. In this case you will have to split the query into two:

1

WHERE RationalisedStreets.Link_ID = RationalisedPointAddress.Link_ID And 
RationalisedMtdArea.Area_ID = RationalisedStreets.L_Area_ID

2

WHERE RationalisedStreets.Link_ID = RationalisedPointAddress.Link_ID And 
RationalisedMtdArea.Area_ID = RationalisedStreets.R_Area_ID

And then append the results together afterwards. This should give the same result.

Alternatively, move the data into a spatial DBMS such as SQL Server or PostGres / PostGIS, where more complex SQL queries are supported (and MapInfo 10 can access the results)

Answer 2

As far as I can tell it doesn't mind logical ORs, just as long as you don't try to use them as part of a join, as you are. For example:

SELECT FROM table WHERE col = 1 OR col = 2

...will work.

If you replace the OR in your code with an AND it will work (inasmuch as it will parse correctly. Obviously it won't give you the correct result).

Sorry that's not much help.