Re: How to join table to itself N times?

On Wed, Mar 20, 2013 at 5:38 PM, W. Matthew Wilson <matt@tplus1.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.

If the number of dimensions is not fixed, then you'll probably have to
write a plpgsql function to first interrogate the data set for how
many dimensions there are and then to build an n-dimension query.
While joining a variable number of tables may be problematic as you
won't have a fixed number of columns, using a union might give you
what you want with a fixed number of columns.