[mapserver-users] Postgis query

[Carlos Ruiz]

I guess the problem resides in the use of INNER and LEFT joins. Also, the order of the 
tables is important. The first tables specified in a query within the joins must be the ones 
which stablish the records agrupation or discrimination.

IC Carlos Ruiz
 

--- On Tue, 12/2/08, mark balman <mark.balman at gmail.com> wrote:

From: mark balman <mark.balman at gmail.com>
Subject: [mapserver-users] Postgis query
To: mapserver-users at lists.osgeo.org
Date: Tuesday, December 2, 2008, 4:37 AM

Hi All

I am trying to output a query from postgis using three tables and it
is not quite working.

First table is a quarter degree grid (spatial table)
Second table is a table with each grid cell id along with many species
id per grid cell
Third table is list of species

My query definition works well as the following (using two tables only)

select fid_1, the_geom AS the_geom, spcgrid.id as gid, spcid
from spcdensity
left join spcgrid on spcdensity.fid_1 = spcgrid.fid

This produces exactly what I want through mapserver, query a grid cell
and it returns a list of species within that grid cell

My problem is that I want to return a list of species names so my
query at present is defined as:

select spcid, spccommonname, fid_1, spcdensity.the_geom, spcid as gid
from spcgrid left join species on spcgrid.spcid = species.spcrecid
inner join spcdensity on spcdensity.fid_1 = spcgrid.fid

Although the query works when run in pgadmin, when I query a grid cell
I get a list of one species repeated. I am slightly confused as to
what I am missing here, can anyone provide me with some pointers?

Thanks in advance

Mark
_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users



      
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20081202/f8f64390/attachment-0001.html