Re: Query problem... - Mailing list pgsql-general

You may want to take a look at the ltree and tablefunc contrib
modules.  They both allow you to do something like this, and the
abstract away the difficulty of query building.  ltree will allow you
to precompute the tree, and the tablefunc module has a connectby()
function for runtime parent-child relationship evaluation.


On Sat, 2 Oct 2004 15:12:46 -0700, Net Virtual Mailing Lists
<mailinglists@net-virtual.com> wrote:
> Hello,
>
> I have 3 tables which are joined that I need to create a summation for
> and I just cannot get this to work.
>
> Here's an example:
>
> CREATE table1 (
>     id1    INTEGER,
>     title1 VARCHAR
> );
> INSERT INTO table1 (1, 'Heading #1');
> INSERT INTO table1 (2, 'Heading #2');
>
> CREATE table2 (
>     id1    INTEGER,
>     id2    INTEGER,
>     title2 VARCHAR
> );
> INSERT INTO table2 (1, 1, 'Category #1.1');
> INSERT INTO table2 (1, 2, 'Category #1.2');
> INSERT INTO table2 (2, 1, 'Category #2.1');
> INSERT INTO table2 (2, 2, 'Category #2.2');
>
> CREATE table3 (
>     id1    INTEGER,
>     id2    INTEGER,
>     id3    INTEGER,
>     title3 VARCHAR
> );
> INSERT INTO table2 (1, 1, 1, 'Sub-Category #1.1.1');
> INSERT INTO table2 (1, 1, 2, 'Sub-Category #1.1.2');
> INSERT INTO table2 (1, 2, 1, 'Sub-Category #1.2.1');
> INSERT INTO table2 (1, 2, 2, 'Sub-Category #1.2.2');
> INSERT INTO table2 (2, 1, 1, 'Sub-Category #2.1.1');
> INSERT INTO table2 (2, 1, 2, 'Sub-Category #2.1.2');
> INSERT INTO table2 (2, 2, 1, 'Sub-Category #2.2.1');
> INSERT INTO table2 (2, 2, 2, 'Sub-Category #2.2.2');
>
> What I am trying to represent is some sort of hierarchical data here, for
> example:
>
> Heading #1
>     Category #1.1
>         Sub-Category #1.1.1
>         Sub-Category #1.1.2
>     Cateogry #1.2
>         Sub-Category #1.2.1
>         Sub-Category #1.2.2
> Heading #2
>     Category #2.1
>         Sub-Category #2.2.1
>         Sub-Category #2.2.2
>     Cateogry #2.2
>         Sub-Category #2.2.1
>         Sub-Category #2.2.2
>
> ... I hope that makes sense.. Perhaps I'm going about this the wrong way
> to begin with?
>
> In any event, the problem is now I have another table which uses these
> tables.  For example:
>
> CREATE TABLE blech (
>    somedata  VARCHAR,
>    id1       INTEGER,
>    id2       INTEGER,
>    id3       INTEGER
> );
>
> INSERT INTO TABLE blech ('Record #1', 1, 1, 1);
> INSERT INTO TABLE blech ('Record #2', 1, 1, 1);
> INSERT INTO TABLE blech ('Record #3', 1, 2, 1);
> INSERT INTO TABLE blech ('Record #4', 1, 1, 2);
> INSERT INTO TABLE blech ('Record #5', 2, 1, 1);
>
> ... etc... (NOTE: id1, id2, and id3 cannot be NULL in this table)
>
> What I want is a query that will give me this:
>
> count |  id1   |   id2   | id3
> ------------------------------
>    4  |   1    |         |
>    3  |   1    |    1    |
>    1  |   1    |    1    |  1
>    1  |   1    |    1    |  2
>    1  |   1    |    2    |
>    1  |   1    |    2    |  1
>    1  |   2    |         |
>    1  |   2    |    1    |
>    1  |   2    |    1    |  1
>
> I've tried all manner of LEFT JOINs, GROUP BYs, and even tried using
> UNION, but I just can't seem to get the results I need.  I'm definitely
> not married to this type of schema, if there is a more efficient way of
> handling this I'm all for it.
>
> Thanks as always!
>
> - Greg
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>