Query problem... - Mailing list pgsql-general

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