Skip site navigation (1) Skip section navigation (2)

Re: Optimal query suggestion needed

From: Richard Huxton <dev(at)archonet(dot)com>
To: Interzone <lists(at)interzone(dot)gr>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Optimal query suggestion needed
Date: 2004-06-17 13:36:34
Message-ID: 40D19E62.8090406@archonet.com (view raw or flat)
Thread:
Lists: 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

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-06-17 13:37:43
Subject: Re: use of a composite type in CREATE TABLE?
Previous:From: Hannes KorteDate: 2004-06-17 11:01:08
Subject: use of a composite type in CREATE TABLE?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group