Thread: problematic query (for me ;-)

I'm trying to get a hint as to how i can solve this problem:

i have a table:
 n | t
---+--- 1 | 2 1 | 5 2 | 3 2 | 5 3 | 4 3 | 3
(6 rows)

now i want to find the pairs (n1,n2) where no t's collide - eg in this 
table it would be (1,3) and (3,1).

Note - this is not homework ;-)

what i'm actually trying to do is to extract n=(countyid,roadid) 
t=housenumber - and find out which roads that *cannot* cross county 
border because their housenumbers collide (to this i've already narrowed 
the n's depending on roadname).

"Kim Petersen" <kp@kyborg.dk> wrote in message
news:3E660A41.7040509@kyborg.dk...
> I'm trying to get a hint as to how i can solve this problem:
>
> i have a table:
>
>   n | t
> ---+---
>   1 | 2
>   1 | 5
>   2 | 3
>   2 | 5
>   3 | 4
>   3 | 3
> (6 rows)
>
> now i want to find the pairs (n1,n2) where no t's collide - eg in this
> table it would be (1,3) and (3,1).

SELECT DISTINCT nt1.n, nt2.n FROM nt nt1, nt nt2
WHERE NOT EXISTS (   (SELECT t from nt where n=nt1.n) INTERSECT   (SELECT t from nt where n=nt2.n))
n | n
---+---1 | 33 |1
(2 rows)

I'm not sure that it scales well to large datasets...

/Bjarke

Kim Petersen wrote:
> I'm trying to get a hint as to how i can solve this problem:

it seems it works to post to a newsgroup - i've been trying to solve 
this for hours - and just came up with a solution - only question now is 
whether or not it is usable (on millons of tuples).

select distinct t1.n,t2.n from test t1,test t2   where not exists     (select t from test where n=t1.n
intersect     select t from test where n=t2.n);
 

optimization ideas would be greatly appreciated.