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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nikolai Cheltsov | 2007-10-24 15:16:00 | delete DB with connection destroy? |
Previous Message | Nis Jørgensen | 2007-10-24 08:05:55 | Re: Postgres Joins ? |