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

Re: Postgres Joins ?

From: Tony Wade <postgres(at)wade(dot)co(dot)za>
To: Nis Jørgensen <nis(at)superlativ(dot)dk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Postgres Joins ?
Date: 2007-10-24 13:25:17
Message-ID: 20071024132517.GN4681@postman.stonemanor.co.za (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, Oct 24, 2007 at 10:05:39AM +0200, Nis Jørgensen wrote:
> Are the values for "content" limited to the above (or another small
> fixed set), or do you need columns for whatever is in the field?
> 
> In the first case, you can do
> 
> select t.id,q.name, t.subject, u.name, t.status, t.created, t.resolved,
>   (select created from objectcustomfieldvalues ov where ov.object_id =
> t.id and ov.customfield = 21 and content = 'Captured') as captured,
> (select created from objectcustomfieldvalues ov where ov.object_id =
> t.id and ov.customfield = 21 and content = 'Released to WH') as
> "released to wh"
> ...
> from tickets t, queues q, users u
> where t.created > '2007-10-02 00:00:00' AND t.queue = 6 and t.queue =
> q.id AND t.owner = u.id order by t.id;
> 
> In the second case, you need the columns to be calculated on the fly.
> There are some functions called crosstab/crosstabN in
> contrib/tablefunc,. I have never used them, but from the documentation
> they look a little bit cumbersome to work with.
> 
> Note that if your columns are not specified, you need to think about the
> order you want them in.
> 
> Nis

Hi Nis, 

Thanks for the notes, I have managed to create the report I wanted with the use of a 
couple views. since the content field varies, the original query can produce up to
4 different results, meaning that each result is a seperate line.

The report now has 7 different views, and a change to the first view, and a select * from 
the 7th view, gives me the data I require.

I'll run the above when I have a little more time, as it may be a cleaner query than what I have now.


Regards, 

Tony

In response to

pgsql-novice by date

Next:From: Nikolai CheltsovDate: 2007-10-24 15:16:00
Subject: delete DB with connection destroy?
Previous:From: Nis JørgensenDate: 2007-10-24 08:05:55
Subject: Re: Postgres Joins ?

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