Query problem...

Started by Net Virtual Mailing Listsover 21 years ago2 messagesgeneral

Jump to latest

Net Virtual Mailing Lists

mailinglists@net-virtual.com

over 21 years ago

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

Import Notes

Reply to msg id not found: 20041002192105.29B165B00AC@svr4.postgresql.orgReference msg id not found: 20041002192105.29B165B00AC@svr4.postgresql.org

Mike Rylander

mrylander@gmail.com

over 21 years ago

In reply to: Net Virtual Mailing Lists (#1)

Re: Query problem...

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:

Show quoted text

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