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

Re: Postgres Joins ?

From: Nis Jørgensen <nis(at)superlativ(dot)dk>
To: Tony Wade <postgres(at)wade(dot)co(dot)za>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Postgres Joins ?
Date: 2007-10-24 08:05:39
Message-ID: 471EFCD3.30100@superlativ.dk (view raw or flat)
Thread:
Lists: pgsql-novice
Tony Wade skrev:
> Hi, 
> 
> Would someone be able to assist with the following Postgres Query. 
> 
> select
> t.id,q.name,t.subject,u.name,t.status,t.created,t.resolved,ov.content,ov.created
> as Updated from tickets t, queues q, users u, objectcustomfieldvalues ov
> where t.created > '2007-10-02 00:00:00' AND t.queue = 6 and t.queue =
> q.id AND t.owner = u.id and ov.objectid = t.id and ov.customfield = 21
> order by t.id;
>
> which returns the following:
> 
> id   |   name    |  subject   |   name    |  status  |    created           |          resolved       | content        |    updated
> -------+-----------+-----------------------------------------------------------------------------------+--------------+----------+--------
> 12345	Fred	   Purchase	 bob	     Resolved	2007-10-21 14:01:23	2007-10-31 09:03:40	Captured	 2007-10-21 14:03:32	
> 12345	Fred	   Purchase	 bob	     Resolved	2007-10-21 14:01:23	2007-10-31 09:03:40	Released to WH   2007-10-22 07:34:01
> 12345	Fred	   Purchase	 bob	     Resolved	2007-10-21 14:01:23	2007-10-31 09:03:40     Delivered 	 2007-10-31 08:58:53
>
> What I'd like to have is the following:
> 
> id   |   name    |  subject   |   name    |  status  | created             |captured            | released to wh     |   delivered         |  resolved
> 12345	Fred	   Purchase	bob	    Resolved   2007-10-21 14:01:23  2007-10-21 14:03:32	 2007-10-22 07:34:01   2007-10-31 08:58:53   2007-10-31 09:03:40
> 
> 
> Is this possible ? I suspect it requires the use of Joins in some manner, but my SQL knowledge is not up to scratch. I'd appreciate
> it if someone could point me in the right direction.

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

In response to

Responses

pgsql-novice by date

Next:From: Nis JørgensenDate: 2007-10-24 08:05:55
Subject: Re: Postgres Joins ?
Previous:From: Tom LaneDate: 2007-10-24 02:38:52
Subject: Re: Determine the date a table was defined/created.

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