Join between Postgis table and Postgresql table

[Steven Monai]

Carlo:

Okay, so let's say that the data relationship is 1 'pippo' to 0-or-more
'opo's, and 1 'opo' to 0-or-1 'pippo's. In order for MapServer to function
correctly, you have to make sure that MapServer gets a unique ID number for
each row of your data. 'pippo.gid' is not unique anymore, since a given
'pippo' could appear more than once in the DATA query. However, the primary
key of 'opo' (I assume 'opo.gid') should suffice. Therefore, try this for
your DATA line:

DATA "the_geom from (SELECT opo.gid, pippo.the_geom, pippo.id_shape,
opo.nome, opo.cognome   FROM pippo LEFT OUTER JOIN opo ON pippo.id_shape =
opo.id_shape) as foo using SRID=4326 using unique gid  "

In a sense, this is inefficient for map rendering, since MapServer will draw
each pippo's geometry a number of times equal to the number of matching
opo's, but it should work. And if you use MapServer's 'nquery' mode for
querying, you should get all matching records, not just one.

Hope this helps,
-SM
--



-----Original Message-----
From: Carlo Calvino [mailto:carlo.calvino at EMAIL.IT] 
Sent: Friday, November 11, 2005 1:32 AM
To: MAPSERVER-USERS at LISTS.UMN.EDU; Steven Monai
Subject: Re: Join between Postgis table and Postgresql table

On Thu, 10 Nov 2005 09:45:58 -0600, Steven Monai <stevem at SPATIALMAPPING.COM>
wrote:

Steven,
it worked!! Thank you very much! :)
But now it remains the most important problem: What about 1-to-many
relationships? I have a shapefile with point features: let's suppose to
click on such a point, i want to see all the analysis (the rows in a
table) related to that point. With your suggestion, I can only view one
analysis. How can I handle this new situation?


>Carlo:
>
>If you look closely at the error message, it is saying that you need an 
>alias for your subquery in the FROM clause. So, try this in your DATA
line:
>
>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) as foo using SRID=4326 using unique gid  "
>
>Hope this helps,
>-SM
>--
>