From: InterZone <lists@interzone.gr>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Optimal query suggestion needed
Date: Thu, 17 Jun 2004 22:06:09 +0300
Message-ID: <40D1EBA1.8080607@interzone.gr> (raw)
In-Reply-To: <40D19E62.8090406@archonet.com>
References: <40D1AEB0.1010604@interzone.gr>
<40D19E62.8090406@archonet.com>
Richard Huxton wrote:
> Interzone wrote:
>
>> Hi all,
>>
>> I'm trying to create a view for a client, but I'm failing miserably so
>> I thought I'ld ask for some help. The initial analysis was quite
>> complicated, and that specific need never came up until recently, and
>> unfortunately, changing the tables is probably not an option :(
>>
>>
>> Given the tables :
>>
>> create table t0 (
>> code integer,
>> address varchar,
>> mun integer
>> )
>>
>> create table t1 (
>> code integer,
>> pname varchar
>> );
>>
>> create table t2 (
>> code integer,
>> t0_fk integer,
>> t1_fk integer,
>> avail bool
>> );
>>
>>
>> I want to create a view that will have:
>> from table t0 the elements "code", "address" and "mun"
>> from table t1 the elements "code" and "pname"
>> from table t2 the total number of elements, and the total number of
>> elements where avail = true, for every value t0_fk (foreign key to t0)
>> and t1_fk (foreigh key to t1).
>
>
> So there's no connection between column "code" in any of the tables? I'm
> confused as to the correlation between t0 and t1. I'm also not clear
> what t2.code is supposed to be.
Sorry, I should be more verbose.
On table t2, the column t0_fk is a foreign key to table t0, and t1_fk is
a foreign key to table t1.
t1 and t0 are totaly independent from each other.
> Could you give a short (5 rows each) example of the contents of the
> tables and the expected results?
>
Well, I could try:
insert into t0 values (1,'area 1',1);
insert into t0 values (2,'area 2',1);
insert into t0 values (3,'area 3',1);
insert into t1 values (1,'ptype : 1');
insert into t1 values (2,'ptype : 2');
insert into t1 values (3,'ptype : 3');
insert into t2 values (1,1,1,TRUE);
insert into t2 values (2,1,2,TRUE);
insert into t2 values (3,1,2,FALSE);
insert into t2 values (4,2,3,TRUE);
insert into t2 values (5,2,1,FALSE);
insert into t2 values (6,2,1,TRUE);
insert into t2 values (7,2,3,FALSE);
insert into t2 values (8,2,3,TRUE);
I want for every pair of t0.code / t1.code to be able to identify the
total entries in t2, and the "available" entries in t2 (among other
things from tables t0 and t1, which are easy to get, so I let them out).
The result should be that (columns intentionally missing):
t0.code---t1.code---count(t2)---avail(t2)----
---1---------1--------1-----------1---------- (t2 row 1)
---1---------2--------2-----------1---------- (t2 rows 2,3)
---2---------1--------2-----------1---------- (t2 rows 5,6)
---2---------3--------3-----------2---------- (t2 rows 4,7,8)
I hope it's clear now (and that I haven't done any mistake that will
confuse things worse).
Thanks in advance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@interzone.gr, dev@archonet.com
Subject: Re: Optimal query suggestion needed
In-Reply-To: <40D1EBA1.8080607@interzone.gr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox