MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » July 2008

Produce set of sets using SQL



fixed

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?





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact