querying 1 to M table

[pippo]

Ciao!

I'm planning to upgrade my website with this database layout:

authors
------
id
first_name
etc

articles
--------
id
pub_date
etc

articles_en
-----------
id
title
content
etc

articles_it
-----------
id
title
content
etc

articles_authors
----------------
article_id
author_id

I'm using mysql but I have installed into the server postgres too.

To obtain an article I can do
SELECT * FROM articles, articles_$lang WHERE articles.id = $id AND articles_$lang.id = $id

To obtain the authors list I can do
SELECT * FROM authors, articles_authors WHERE author_id = authors.id AND article_id = $id

ehm, ehm I did not test them...

I ask if it is possible to merge those two queries.
Practically I want to obtain the article data and all the authors of that article with a single query.
Is that possible ?
Has a DB design sense what I'm asking for ?

As I said previously I could use postregs too,
but actually I would prefer mysql.
I'm using php.

Thank you in advance...

[spartan]

well you could... in a way, but you would get a result that duplicates all the article info.

lets say you had 3 authors for article number 4

you're result would look like this

Article_id  publish_date   Author_name 
04          08/06/2001      J. Smith
04          08/06/2001      C. Peterson
04          08/06/2001      P. Roberts

[pippo]

Thanx for the tips!

If I keep the query separated when I will have to show the list of all articles and related authors the situation will became more complicated.

So maybe changing the database layout as:

authors
------
id
first_name
etc

articles
--------
id
pub_date
author1
author2
etc

articles_en
-----------
id
title
content
etc

articles_it
-----------
id
title
content
etc

could help me a bit.
Then with two LEFT JOIN I will do the trick.

Typically I will always have only one authors for articles, but for books it could be not always true.
Actually the worst case is two authors for the same book.

When one day I will have three authors for a book I will add a column to the table and I will have to do three LEFT JOIN.

Most of the time author2 will be an empty cell.

I'm not too expert with dbs so every suggestion will be appreciated!