From: Interzone <lists@interzone.gr>
To: pgsql-sql@postgresql.org
Subject: Optimal query suggestion needed
Date: Thu, 17 Jun 2004 14:46:08 +0000
Message-ID: <40D1AEB0.1010604@interzone.gr> (raw)
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).
After several attempts and changes as the requirements changed, I finaly
came up with that :
select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname
count(t2.code) as t2total, (select count(t2.code) as t2avail from t2
where t2.avail = true and t2.t0_fk=t0.code and t2.t1_fk = t1.code) as
t2avail from t0, t1, t2 where t2.t0_fk = t0.code and t2.t1_fk=t1.code
group by t0.code, t0.address, t0.mun, t1.code, t1.pname
but it gives wrong results...
I tried several other queries that are extremely slow.
Any help will be greatly appreciated.
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
Subject: Re: Optimal query suggestion needed
In-Reply-To: <40D1AEB0.1010604@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