Re: Postgres Joins ?

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