473,324 Members | 2,541 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 1149
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Wescotte | last post by:
Here is a small sample program I wrote in PHP (running off Apache 1.3.31 w/ PHP 5.0.1) to help illustrates problem I'm having. The data base is using DB2 V5R3M0. The client is WinXP machine using...
3
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going...
1
by: Jeff Blee | last post by:
I hope someone can help me get this graph outputing in proper order. After help from Tom, I got a graph to display output from the previous 12 months and include the average of that output all in...
8
by: Adam Louis | last post by:
I would like help resolving this problem. I'm a novice who's been hired to query a hospital database and extract useful information, available to me only in a dynamically generated, downloadable...
6
by: Martin Lacoste | last post by:
Ok, before I headbutt the computer... don't know why when I add criteria in a query, I get an 'invalid procedure call'. I also don't know why after searching the help in access, the various access...
4
by: Apple | last post by:
1. I want to create an autonumber, my requirement is : 2005/0001 (Year/autonumber), which year & autonumber no. both can auto run. 2. I had create a query by making relation to a table & query,...
11
by: Andy_Khosravi | last post by:
My problem: I'm having trouble with a query taking much too long to run; a query without any criteria evaluating only 650 records takes over 300 seconds to run (over the network. On local drive...
4
by: Konrad Hammerer | last post by:
Hi! I have the following problem: I have a query (a) using another query (b) to get the amount of records of this other query (b), means: select count(MNR) as Number from...
4
by: Stan | last post by:
I am using MS Office Access 2003 (11.5614). My basic question is can I run a query of a query datasheet. I want to use more that one criteria and can not get that query to work. I thought I...
2
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.