Posted by strawberry on 08/09/06 09:33

Anne Bos wrote:
> Op 7 Aug 2006 18:15:06 -0700 schreef "strawberry"
> <zac.carey@gmail.com>:
>
> >
> >Rik wrote:
> >> Anne Bos wrote:
> >> > Op 5 Aug 2006 12:22:36 -0700 schreef "strawberry"
> >> > <zac.carey@gmail.com>:
> >> >
> >> >>
> >> >> 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;
> >> >
> >> > The problem then is how to determine what is a group. The articles
> >> > written by more authors are subgroups of the querry result.
> >>
> >> How do you mean that exactly?
> >> Tell us your table structure, and your current query, and we'll have an
> >> easier time to steer you in the right direction.
> >>
> >> I think strawbarry gave you a great starting point:
> >> GROUP_CONCAT([DISTINCT] expr [,expr ...]
> >> [ORDER BY {unsigned_integer | col_name | expr}
> >> [ASC | DESC] [,col_name ...]]
> >> [SEPARATOR str_val])
> >>
> >> Something like (untested):
> >>
> >> SELECT
> >> articles.article_id,
> >> articles.article_title,
> >> GROUP_CONCAT(DISTINCT authors.author_name SEPARATOR ' and ')
> >> FROM articles
> >> JOIN autart
> >> ON articles.article_id = autart.article_id
> >> JOIN authors
> >> ON autart.author_id = authors.author_id
> >> WHERE article.article_title LIKE '%searchstring%'
> >> GROUP BY articles.article_id, articles.article_title
> >>
> >> Grtz,
> >> --
> >> Rik Wasmus
> >
> >Ah, you've given the game away ;-).
> >
> >Yep, unless Rik and I are missing something, that's pretty much spot
> >on. I'd give the GROUP_CONCAT an alias - and all those 'ands' could get
> >a little tedious if the article was by several authors (as academic
> >articles so often are). Also, the article_id isn't strictly necessary
> >Anne in the final results unless you want to be able to provide a link
> >back to the article itself, say, through a hyperlink in a little bit of
> >php. Oh, and to preserve my sanity (if it's not too late), I'd actually
> >rename the autart table!!!:
> >
> >SELECT article, authors FROM
> >(
> >SELECT DISTINCT authors_articles.article_id,articles.article,
> >GROUP_CONCAT(authors.author ORDER BY author) AS authors
> >FROM authors_articles
> >LEFT JOIN articles ON authors_articles.article_id = articles.article_id
> >LEFT JOIN authors ON authors_articles.author_id = authors.author_id
> >WHERE article LIKE '%searchstring%'
> >GROUP BY article_id) t1
> >;
>
> I see,
> actually I looked in php for a solution.
> My tables are:
> AUTHORS with the fields idauth, author,
> ARTICLES with the fields idart, title (and some others irrelevant for
> now)
> AUTART with the fields ID, idauth, idart.
>
> Then:
> SELECT author.author, articles.idart, articles.title (and yet a few)
> FROM author, autart, articles
> WHERE author.idauth = autart.idauth AND articles.idart = autart.idart
> AND articles.title LIKE '%$search%' ORDER BY articles.idart.
>
> (Actually it is in Dutch. I translated some names. So in my real
> database the tables are AUTEUR, with idschr, schrijver, ARTIKEL with
> idart, titel and CREATIE with ID, idschr, idart. It is less confusing
> than in English.)
>
> In this way I get a list the way I already described. I hoped that by
> ordering this way two or more numbers representing the idarts could be
> detected so that in those cases I could do something like $author =
> $author(i-1). ", ". $author(i).
>
> Greetings,
> Anne Bos

I think that you could implement a php solution more or less in the
manner you suggest - and the code could be simpler than you propose,
but I strongly suspect it would be significantly slower than either of
the 'purer' mysql methods outlined above.

Also, although there's nothing wrong with it, you don't actually need
that ID field in the autart/creatie because the idschr and idart fields
together can serve as the primary key - the logic being that the same
author is not going to write the same article twice!

Do the queries already provided seem in some way inadequate? They
should give the results exactly as you had intended.

[Back to original message]