MS SQL Server - MIPRO - Workaround to execute Inner Join
Posted on 2007-07-20
MS SQL Server
My problem now is that I have a COTS GIS (MapInfo Pro) inerfaced directly with my SQL Server database. The data on a large number of spatial entities is stored in the dB in two tables, a common maser table (Country_Facility) and a specialist table for each Facility Type (eg Facility_Education). Location_Code is the common PK.
Aftre much struggling MapInfo Corp have admitted there is a known bug in their software that prevents the correct parsing of an INNER JOIN when quering an external SQL database. They have offered some workarounds but I cannot get the SQL approach to work and need some help please. I have tried a number of options based on the excellent assistance I have received recently but still to no avail.
MapInfo offered this as an example workaround:
BUT your right the Expert Mode dialog isn't parsing the INNER JOIN correctly. It is a known bug.
1. A inner join can be performed by passing the following query:
select * from CITY1K, USA where CITY1K.STATE=USA.STATE My version looks like :-
From TDB_USER.Country_Facility c, TDB_USER.Facility_Education f
WHERE c.Location_Code = f.Location_Code
AND c.Nation_ID like '6%'
I get Error 'Ambiguous column name 'Location_Code'
The second work around is to create a 'View' in SQL Server which performs the required INNER JOIN and then access the resultant virtual table. This would be a excellent solution but although each table pair has a common PK, MapInfo doesnt seem able to 'see' the Primary Key and in its absence cannot make the table 'editable' which is essential.
Very Grateful for any ideas