[mapserver-users] non spatial MSSQL data in a Layer JOIN

[Jeff McKenna]

and my advice is to follow what Daniel said earlier, focus on getting
the join to work through ogrinfo; don't even test with MapServer until
you have it working there (just too many other things could be happening
with MapServer).  Connect to ogrinfo to your shapefile, then pass the
-sql switch with the left join syntax.  Once I am able to that, then I
would move to shp2img testing.

-jeff


-- 
Jeff McKenna
MapServer Consulting and Training Services
http://www.gatewaygeomatics.com/



On 2014-01-09 1:09 PM, Brent Fraser wrote:
> Ken,
> 
>   In my example note the ",basic" (that's my table name):
> 
>     DATA "SELECT * FROM t_505033 LEFT JOIN
> 'ODBC:Parcels_Rural,basic'.basic ON t_505033.PID=basic.PID"
> 
> I may have included it for performance reasons (so one that table is
> enumerated), but it may be required.  It does seem redundant to say:
> ,basic'.basic
> 
> and I saw your email re successfully joining to a new table. Maybe the
> key type of the old table is not compatible?
> 
> Just grasping at straws...
> 
> Best Regards,
> Brent Fraser
> 
> On 1/9/2014 9:07 AM, KenHeer1 wrote:
>> Again thanks for all the help. We're on the same page, what I have in my
>> mapfile definition is:
>>
>> CONNECTIONTYPE OGR
>> CONNECTION "C:/Path/to/shapefile.shp"
>> DATA "SELECT shapefile.*, tablename.* FROM shapefile LEFT JOIN
>> 'MSSQL:server=server\instance;database=dbname;UID=userid;PWD=password;'.tablename
>>
>> ON shapefile.fieldkey = tablename.fieldkey"
>>
>> and running ogrinfo I have:
>>
>> ogrinfo -sql "select shapefile.*, tablename.* from shapefile left join
>> 'MSSQL:server=server\instance;database=dbname;UID=userid;PWD=password;'.tablename
>>
>> ON shapefile.fieldkey = tablename.fieldkey" C:/Path/to/shapefile.shp
>>
>> When I try either I get an error saying my table doesn't exist. The
>> specific
>> error I get is "SELECT from table zone failed, no such
>> table/featureclass"
>> (zone being the table I'm trying to get to).
>>
>> Using ogrinfo, I've tried the connection to the shapefile by itself
>> and it
>> works, and I've tried the connection to the OGR database and it works.
>> I can
>> query out of each of them fine, but when I attempt the join it says the
>> table (that I was just able to query) doesn't exist.
>>
>> Thanks
>>
>>