[mapserver-users] Postgis query

[mark balman]

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