pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

problematic query (for me ;-)
2+ messages / 2 participants
[nested] [flat]

* problematic query (for me ;-)
@ 2003-03-05 14:31  Kim Petersen <kp@kyborg.dk>
  0 siblings, 1 reply; 2+ messages in thread

From: Kim Petersen @ 2003-03-05 14:31 UTC (permalink / raw)
  To: pgsql-sql

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).




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: problematic query (for me ;-)
@ 2003-03-06 01:44  Bjarke Dahl Ebert <bebert@tiscali.dk>
  parent: Kim Petersen <kp@kyborg.dk>
  0 siblings, 0 replies; 2+ messages in thread

From: Bjarke Dahl Ebert @ 2003-03-06 01:44 UTC (permalink / raw)
  To: pgsql-sql

"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 | 3
 3 |1
(2 rows)

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

/Bjarke






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-03-06 01:44 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-05 14:31 problematic query (for me ;-) Kim Petersen <kp@kyborg.dk>
2003-03-06 01:44 ` Bjarke Dahl Ebert <bebert@tiscali.dk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox