|
|
Posted by Rik on 08/07/06 13:19
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
Navigation:
[Reply to this message]
|