By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,811 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,811 IT Pros & Developers. It's quick & easy.

Query problem...

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.