Re: Postgres Joins ?

[Nis Jørgensen <nis(at)superlativ(dot)dk>]

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