Thread: query concat
Hi List, First, Thanks for your colaboration Richard Huxton "Do a search on aggregate functions and "concat" in the mailing list archives, also see the Postgresql Cookbook on techdocs.postgresql.org, I think there might be something there for you." I have been written this mail because I din't find the solution for my problem. Please, I need your colaboration,I like a table or view with this information from 2 tables "ticket" and "orden_respuesta". If you see the table "orden_respuesta", I have many numorden's for one numtti. and in the table "ticket" I have a tti with information about user. The Table "result"(I like this information) numtti | numorden | tt | usuario | estado | ----------+---------------------------+--------+---------+---------+ TTI02000006| ORD02000044, ORD02000045..| 100029 | joroza | CERRADO | TTI02000007| ORD02000061, ORD02000064 | 100051 | pdorado | REVISION| TTI02000008| ORD02000042, ORD02000043 | 56729 | joroza | CERRADO | (I have this tables as information) Table "ticket" tti | tt | usuario | estado | -------------+--------+---------+----------+ TTI02000006 | 100029 | joroza | CERRADO | TTI02000007 | 100051 | pdorado | REVISION | TTI02000008 | 100049| joroza | CERRADO | Table "orden_respuesta" numorden | numtti ------------+------------- ORD02000046 | TTI02000006 ORD02000045 | TTI02000006 ORD02000044 | TTI02000006 ORD02000064 | TTI02000007 ORD02000061 | TTI02000007 ORD02000043 | TTI02000008 ORD02000042 | TTI02000008 Regards, Ricardo P.D: Sorry for my grammar I'm from Bolivia, but I understand Enghish _________________________________________________________________ MSN Fotos: la forma más fácil de compartir e imprimir fotos. http://photos.msn.es/support/worldwide.aspx
On Fri, 20 Sep 2002, [iso-8859-1] Ricardo Javier Aranibar Le�n wrote: > Hi List, > First, Thanks for your colaboration Richard Huxton "Do a search on aggregate > functions and "concat" in the mailing list archives, > also see the Postgresql Cookbook on techdocs.postgresql.org, I think there > might be something there for you." > > I have been written this mail because I din't find the solution for my > problem. For example, an aggregate like: http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=139 (available in the aggregates section of the cookbook - note there's a bug and you need to double the quotes around the ', ') Then you can use a left join and the comma aggregate, something like: select ticket.numtti, ticket.tt, ticket.usuario, ticket.estado,comma(order_respuesta.numorden) from ticket left outer join orden_respuesta on (tti=numtti) group by ticket.numtti, ticket.tt, ticket.usuario, ticket.estado;
On Friday 20 September 2002 16:46, Ricardo Javier Aranibar León wrote: > Hi List, Hello > > The Table "result"(I like this information) > numtti | numorden | tt | usuario | estado | > ----------+---------------------------+--------+---------+---------+ > TTI02000006| ORD02000044, ORD02000045..| 100029 | joroza | CERRADO | > TTI02000007| ORD02000061, ORD02000064 | 100051 | pdorado | REVISION| > TTI02000008| ORD02000042, ORD02000043 | 56729 | joroza | CERRADO | > Normally I would say use a "create view as select ... .". but I am not sure if this will display your needs. Do you really need the data like "ORD02000044, ORD02000045..." ? Then you should use an array or set datatype. They are normally not nice to handel. regards -andreas -- Andreas Schmitz - Phone +49 201 8501 318 Cityweb-Technik-Service-Gesellschaft mbH Friedrichstr. 12 - Fax +49 201 8501 104 45128 Essen - email a.schmitz@cityweb.de