Query one to many

[jgnasser]

I have three tables; Articles, ArticleAuthors and Authors. The Articles
table has a list of journal Articles with a primary key field of
article_id. The Author table lists all authors, with a primary key
field author_id. The ArticleAuthors table links the two, it has foreign
keys for both tables (article_id and author_id). I added another field
to ArticleAuthors (authorno) which shows the number of the author in the article (eg first author, second author etc) is to take care of papers with many authors. Author number 1 has the number 1entered under this field.

The trouble now is how to display each article with all its authors. I
successfully displayed one author using this sql statement:

SELECT Articles.article_id, Articles.date, Articles.title,
Articles.pages, authors.lastname, authors.othername,
articleauthors.authorno, articleauthors.authorno
FROM authors INNER JOIN (Articles INNER JOIN articleauthors ON
Articles.article_id = articleauthors.article_id) ON authors.authors_id =
articleauthors.author_id
WHERE ((articleauthors.authorno)=1 );

How could I add another column to the query which would display Author
number 2, 3 or 4 for the article if they exist and leave blank if not?

 

[jgnasser]

Actually this lists one article as many times as the number of authors but I needed it listed just once.