Re: How to join table to itself N times?

On Wed, Mar 20, 2013 at 7:38 PM, W. Matthew Wilson <matt(at)tplus1(dot)com> wrote:

> I got this table right now:
>
> select * from market_segment_dimension_values ;
> +--------------------------+---------------+
> | market_segment_dimension | value |
> +--------------------------+---------------+
> | geography | north |
> | geography | south |
> | industry type | retail |
> | industry type | manufacturing |
> | industry type | wholesale |
> +--------------------------+---------------+
> (5 rows)
>
> The PK is (market_segment_dimension, value).
>
> The dimension column refers to another table called
> market_segment_dimensions.
>
> So, "north" and "south" are to values for the "geography" dimension.
>
> In that data above, there are two dimensions. But sometimes there could
> be just one dimension, or maybe three, ... up to ten.
>
> Now here's the part where I'm stumped.
>
> I need to create a cartesian product of the dimensions.
>
> I came up with this approach by hard-coding the different dimensions:
>
> with geog as (
> select value
> from market_segment_dimension_values
> where market_segment_dimension = 'geography'),
>
> industry_type as (
> select value
> from market_segment_dimension_values
> where market_segment_dimension = 'industry type')
>
> select geog.value as g,
> industry_type.value as ind_type
> from geog
> cross join industry_type
> ;
> +-------+---------------+
> | g | ind_type |
> +-------+---------------+
> | north | retail |
> | north | manufacturing |
> | north | wholesale |
> | south | retail |
> | south | manufacturing |
> | south | wholesale |
> +-------+---------------+
> (6 rows)
>
> But that won't work if I add a new dimension (unless I update the query).
> For example, maybe I need to add a new dimension called, say, customer
> size, which has values "big" and "small". A
>
> I've got some nasty plan B solutions, but I want to know if there's some
> solution.
>
> There's a really elegant solution in python using itertools.product, like
> this:
>
> >>> list(itertools.product(*[['north', 'south'], ['retail',
> 'manufacturing', 'wholesale']]))
>
> [('north', 'retail'),
> ('north', 'manufacturing'),
> ('north', 'wholesale'),
> ('south', 'retail'),
> ('south', 'manufacturing'),
> ('south', 'wholesale')]
>
> All advice is welcome. Thanks in advance!
>
> Matt
>
>
>
> --
> W. Matthew Wilson
> matt(at)tplus1(dot)com
> http://tplus1.com
>
>
You may try:

Select a.value, b.value
from market_segment_dimension_values as a,
from market_segment_dimension_values as b
where a.market_segment_dimension <> b.market_segment_dimension

-- AI