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:[EMAIL PROTECTED] Sent: Friday, November 11, 2005 1:32 AM To: [email protected]; Steven Monai Subject: Re: Join between Postgis table and Postgresql table On Thu, 10 Nov 2005 09:45:58 -0600, Steven Monai <[EMAIL PROTECTED]> 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 >-- >
