[postgis-users] OT: SQL problem/challenge

[Stephen Woodbridge]

Hi,

I have a challenging SQL problem, that I thought some of you might be 
able and interested to try and solve.

My problem is that I need to compute some number of separate and costly 
queries as represented VALUES clauses below. And I need to eliminate the 
overlaps in the adjacent sub-selects, but not globally from the results.

So in this example
gid 6 and 7 would get eliminated from A and B and
gid 11 and 12 would get eliminated from B and C but
gid 1 would not get eliminated.

Sadly I'm currently working on Postgresql 8.3.9 other wise I think using 
the WITH clause in 8.4 for would help.

So at this point I'm kind of thinking that the only way I can do this 
with 8.3 is to  "create temp table leg1 as select ..."  and then work 
with the temp tables. This will be running in a plpgsql function it that 
matters.

I would be interested in ideas/examples for both 8.3 and 8.4.

Thanks,
   -Steve

Here is a piece of not working SQL:

select *
from (
   select gid, aa from
     (values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7)) as A (gid, aa)
   union all
   select gid, aa from
     (values (7,7),(6,6),(8,8),(9,9),(10,10),(11,11),(12,12)) as B (gid, aa)
   union all
   select gid, aa from
     (values (12,12),(11,11),(13,13),(14,14),(15,15),(16,16),(1,1)) as C 
(gid, aa)
) as foo
-- the where clause does not work, specifically the sub-select
-- this does global elimination,
-- and it needs to be locally between sub-selects
where gid not in (
     (select gid from A intersect select gid from B)
        union
     (select gid from B intersect select gid from C)
   )