On Tue, 27 May 2003, Richard Huxton wrote: > On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote: > > So, I have the same problem, but I need all authors for each publication > > to show up in it's own column. I tried the full join query from a > > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead > > of ALL authors at once. > > > > I'll do some RTFMing of the joins.. and post any results I get > > > > BUT, right now, looping over each publication with php isn't taking that > > long. I would like to know how to make this query, though! > > > > Please let me know how to get a result like: > > ____________________________________________________ > > > > |All Authors|Title|Source|Year|Type|Length|Keywords| > > Well, if you search the archives for terms "text", "concat", "aggregate" you > should come up with one solution. This involves writing your own aggregate > function, like SUM() but for text. Don't worry, it's not difficult. The only > issue is that you won't be able to guarantee the order of authors in the > field. > > There might be something on this in my PostgreSQL Notes on > http://techdocs.postgresql.org/ too - can't remember for sure. > > -- > Richard Huxton
A function is definitely in need. But I already have PHP doing something of this sort. I realize that PostgreSQL would most likely do it faster. What I meant by the result output above was that there would be _many_ results, but for each publication only ONE column for ALL authors in EACH row. (*whew*) So perhaps a function, but not an aggregate function. I'll look into functions next, as it seems that there is no single SQL statement that can do this... I can't see how. -Chadwick ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
