Posted by strawberry on 08/05/06 19:22

Anne Bos wrote:
> Hi everybody,
> I have a small database of a journal. It contains a table AUTHORS,a
> table ARTICLES and a link-table AUTART.
> Constructing a query asking for articles having some special word in
> the title and showing the results in a table is not that difficult,
> but if a resulting article has two or more authors, I get the
> following:
> Adamson, A. Some new ideas
> Bacardi, C. Some new ideas
> etc.
> I want in such case show the result like this:
> Adamson, A. and Bacardi, C. Some new ideas.
> (And of course more people likewise if an article has more authors.)
>
> I understand that, to do that I have to compare row i and row i+1 and
> act appropriately if these rows have identical results in the
> articles-column.
> I'm sure it has a simple solution but somehow I'm struck with
> blindness in this respect.
>
> Can anybody help?
> Thanks in advance,
> Anne Bos

Have a look at GROUP_CONCAT

Something like this, although this isn't quite the answer - that would
be too easy :-):

SELECT articles.article_id,GROUP_CONCAT(autart.author_id)
FROM articles
LEFT JOIN autart ON autart.article_id = articles.article_id
GROUP BY article_id;

[Back to original message]