Обсуждение: Postgres Joins ?
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, queuesq, users u, objectcustomfieldvalues ov where t.created > '2007-10-02 00:00:00' AND t.queue = 6 and t.queue = q.id ANDt.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-2114:03:32 12345 Fred Purchase bob Resolved 2007-10-21 14:01:23 2007-10-31 09:03:40 Released to WH 2007-10-2207:34:01 12345 Fred Purchase bob Resolved 2007-10-21 14:01:23 2007-10-31 09:03:40 Delivered 2007-10-3108: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'dappreciate it if someone could point me in the right direction. Regards, Tony Wade postgres@wade.co.za
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-2114: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'dappreciate > 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
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-2114: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'dappreciate > 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 decide which order you want them in. Nis
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