Subject: Produce set of sets using SQL From: Adam Glauser Date: Fri, 25 Jul 2008 12:37:59 -0400 List-archive: List-help: List-id: Midrange Systems Technical Discussion List-post: List-subscribe: , List-unsubscribe: ,

I am trying to get an idea of how often we have trucks take a partial
load from one terminal to another, and then continue to (an)other terminal(s) with the rest of the freight. I'm also trying to determine if certain combinations of terminals are much more common than others.

Here is a simplified example which outlines the problem. Suppose we
have a table like this:

k1 k2 dest
a a 1
a a 2
a b 3
b c 4
b c 5
b c 6

(k1,k2) constitutes a unique identifier for a load, and each load has
one or more destinations. I would like to end up with ((1,2), (3),
(4,5,6)), that is, a set of all combinations of destinations which have
been stops on the same load. I'd then like to count how many loads have
used a particular combination, but that's probably best as a separate step.

I can use a query like this
select a.k1,a.k2,a.dest, coalesce(b.dest,0), coalesce(c.dest,0)
from testtable a
left outer join testtable b
on a.k1=b.k1 and a.k2 = b.k2
and a.dest<>b.dest

left outer join testtable c
on b.k1=c.k1 and b.k2=c.k2
and a.dest<>c.dest and b.dest<>c.dest

order by k1,k2

which results in

k1 k2 dest dest dest
a a 1 2 0
a a 2 1 0
a b 3 0 0
b c 4 5 6
b c 4 6 5
b c 5 4 6
b c 5 6 4
b c 6 4 5
b c 6 5 4

This clearly gets cumbersome as the number of possible combinations increases.

Am I tilting at windmills, or just missing the boat?