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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
