469,106 Members | 2,313 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

Query problem...

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
1 1037
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
<ma**********@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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Adam Louis | last post: by
6 posts views Thread by Martin Lacoste | last post: by
11 posts views Thread by Andy_Khosravi | last post: by
4 posts views Thread by Stan | last post: by
2 posts views Thread by existential.philosophy | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.