pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Optimal query suggestion needed
6+ messages / 4 participants
[nested] [flat]
* Re: Optimal query suggestion needed
@ 2004-06-17 13:36 Richard Huxton <dev@archonet.com>
parent: Interzone <lists@interzone.gr>
1 sibling, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2004-06-17 13:36 UTC (permalink / raw)
To: Interzone <lists@interzone.gr>; +Cc: pgsql-sql
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.
Could you give a short (5 rows each) example of the contents of the
tables and the expected results?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Optimal query suggestion needed
@ 2004-06-17 14:46 Interzone <lists@interzone.gr>
0 siblings, 2 replies; 6+ messages in thread
From: Interzone @ 2004-06-17 14:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimal query suggestion needed
@ 2004-06-17 17:54 Bruno Wolff III <bruno@wolff.to>
parent: Interzone <lists@interzone.gr>
1 sibling, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2004-06-17 17:54 UTC (permalink / raw)
To: Interzone <lists@interzone.gr>; +Cc: pgsql-sql
On Thu, Jun 17, 2004 at 14:46:08 +0000,
Interzone <lists@interzone.gr> wrote:
> 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
This approach is actually pretty close. I think you just didn't pick a
good way to count the avail = true rows.
I think you can replace the above with:
select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname
count(t2.code) as t2total, count(case when t2.avail then 1 else NULL) 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimal query suggestion needed
@ 2004-06-17 19:06 InterZone <lists@interzone.gr>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: InterZone @ 2004-06-17 19:06 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimal query suggestion needed
@ 2004-06-17 19:22 InterZone <lists@interzone.gr>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 6+ messages in thread
From: InterZone @ 2004-06-17 19:22 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
Bruno Wolff III wrote:
> On Thu, Jun 17, 2004 at 14:46:08 +0000,
> Interzone <lists@interzone.gr> wrote:
>
>>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
>
>
> This approach is actually pretty close. I think you just didn't pick a
> good way to count the avail = true rows.
> I think you can replace the above with:
> select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname
> count(t2.code) as t2total, count(case when t2.avail then 1 else NULL) 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
Thanks
the query you sent failed on v. 7.4, so I added an "end" to the case
statement. I selected from the tables and the results seem to be correct.
I rewrite it for archiving reasons:
select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname
count(t2.code) as t2total, count(case when t2.avail then 1 else NULL
end) 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
Once again thank you.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimal query suggestion needed
@ 2004-06-17 20:01 Bruno Wolff III <bruno@wolff.to>
parent: InterZone <lists@interzone.gr>
0 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2004-06-17 20:01 UTC (permalink / raw)
To: InterZone <lists@interzone.gr>; +Cc: pgsql-sql
On Thu, Jun 17, 2004 at 22:22:34 +0300,
InterZone <lists@interzone.gr> wrote:
>
> Thanks
> the query you sent failed on v. 7.4, so I added an "end" to the case
> statement. I selected from the tables and the results seem to be correct.
That was an unintentional typo.
>
> I rewrite it for archiving reasons:
>
> select t0.code, t0.address, t0.mun, t1.code as t1code, t1.pname
> count(t2.code) as t2total, count(case when t2.avail then 1 else NULL
> end) 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
>
>
> Once again thank you.
Your welcome.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-06-17 20:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-17 14:46 Optimal query suggestion needed Interzone <lists@interzone.gr>
2004-06-17 13:36 ` Richard Huxton <dev@archonet.com>
2004-06-17 19:06 ` InterZone <lists@interzone.gr>
2004-06-17 17:54 ` Bruno Wolff III <bruno@wolff.to>
2004-06-17 19:22 ` InterZone <lists@interzone.gr>
2004-06-17 20:01 ` Bruno Wolff III <bruno@wolff.to>
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