[postgis-users] Advanced query problem

[Bill Binko]

On Sat, 2006-02-25 at 11:36 -0500, Krishnaa Sridharan wrote:
> Hi all,
> I am new to Postgis and was working on the advanced query shown in the
> manual. When i did the query everything worked well. But I have a
> problem when querying something like this...
>  
> Table one (ROOMS) with example data (A proper table with spatial ref)
>  
> room_no 
> 101
> 102
> ....
> Table two (MAINT) with example data(table to get data from)
>  
> room_no       maint_details
> 101               Fixed bulbs
> 101               Fixed lheat
> .......
>  
> when i do a query like this:
> the_geom FROM (SELECT rooms.the_geom AS the_geom, rooms.oid AS oid,
> maint.maint_details AS maint_details, FROM rooms LEFT JOIN maint ON
> rooms.room_no = maint.room_no) AS new_table USING UNIQUE oid USING
> SRID=-1
>  
> it seems to work well on points with just one value. But if there are
> multiple values in the second table as shown, The first name repeats
> the number of times it is in the table. is there a way to fix this. I
> am really new to this please help me

I'm not really sure this is broken :)

Whether or not you use LEFT JOIN, you will always have multiple entries
if there are multiple entries in the MAINT table.  That's just how joins
work.
You'll have output that looks like this:

the_geom   | oid | maint_details
POLYGON(..   001   Fixed bulbs
POLYGON(..   001   Fixed lheat

Because of the LEFT JOIN (instead of a full join), you'll also get rows
like this:

the_geom   | oid | maint_details
POLYGON(..   001   Fixed bulbs
POLYGON(..   001   Fixed lheat
POLYGON(..   002   NULL

The LEFT JOIN just means you won't omit any rows from rooms because they
don't have any matching rows in MAINT.

Mapserver will not like on the query side, because you're claiming that
oid is unique, while in this case, it's not.

What are you trying to do?  If you want to just take the first row that
matches in MAINT, then you can do this:

the_geom FROM (SELECT rooms.the_geom, rooms.oid, maint.maint_details,
FROM rooms LEFT JOIN maint ON rooms.room_no = maint.room_no LIMIT 1) AS
subq USING UNIQUE oid USING SRID=-1

Almost certainly, the SRID = -1 will cause you problems down the road:
however, that's another discussion.

Bill

-- 
Bill Binko <bill at binko.net>