|
|
Posted by strawberry on 08/08/06 01:15
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
;
Navigation:
[Reply to this message]
|