Join between Postgis table and Postgresql table

[Carlo Calvino]

On Wed, 9 Nov 2005 11:34:58 -0800, Paul Ramsey <pramsey at REFRACTIONS.NET>
wrote:

>No, no, do it on the fly, using the "arbitrary SQL" feature of the
>Mapserver PostGIS connector:
>
>DATA "the_geom from (SELECT g.gid, g.the_geom, a.name, a.description
>FROM geometries g, attributes a WHERE g.id = a.id) using SRID=4326
>using unique gid"
>
>Note you have to have a geometry and unique id in your output result,
>and you have to specify the output SRID.  Any SQL is valid in between
>the (), just make sure you're always outputting a geometry and id.

Thank you very much for your help. I tried your suggestion with this line
inside the .map file:
DATA "the_geom from (SELECT pippo.gid, pippo.the_geom, pippo.id_shape,
opo.nome, opo.cognome   FROM pippo LEFT OUTER JOIN opo ON pippo.id_shape =
opo.id_shape) using SRID=4326 using unique gid  "

Here the relationship between pippo(shape) and opo (data) is 1-to-1.
But mapserver (cgi) gives me this error:

msDrawMap(): Image handling error. Failed to draw layer named 'cata'.
prepare_database(): Query error. Error executing POSTGIS DECLARE (the
actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from
(SELECT pippo.gid, pippo.the_geom, pippo.id_shape, opo.nome, opo.cognome
FROM pippo LEFT OUTER JOIN opo ON pippo.id_shape = opo.id_shape) WHERE
the_geom && setSRID('BOX3D(2766010.83333333 4492764,2781280.16666667
4504216)'::BOX3D, 4326 )'

Postgresql reports the error as 'ERROR: subquery in FROM must have an
alias HINT: For example, FROM (SELECT ...) [AS] foo. '


What is the matter with that? Moreover, I need 'one-to-may' relationship,
and as you wrote, it seems to be inapplicable in this case. How could I
handle 1-to-many relationship with postgis?