Re: Postgres Joins ?

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