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

[Brent Fraser]

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
>
>
>
> --
> View this message in context: http://osgeo-org.1560.x6.nabble.com/non-spatial-MSSQL-data-in-a-Layer-JOIN-tp5096980p5097172.html
> Sent from the Mapserver - User mailing list archive at Nabble.com.
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>