Many-to-many query question

Hi everybody,
I have a question on a query involving a many-to-many relationship that I can't seem to figure out, I was hoping that one of you might be willing to lend me a hand with this.

I will give a simple example here, my real life problem is with a lot more tables, but once I have the answer to this "simple" example I'll be able to translate the solution to my
more-difficult-to-explain-briefly problem.

Let's say you have books and authors. An author can have written many books and a book can have many authors. So I could have three tables as follows:

author_id | author_name

    1          |  Edwards
    2          |  Williams
    3          |  Winston


book_id | book_name
    1       | MySQL
    2       | PHP
    3       | Apache

And then the joining table:

join_id | book_id | author_id

   1      |    1         |   1
   2      |    1         |   2
   3      |    2         |   1
   4      |    2         |   3
   5      |    3         |   3

Now let's say I wan't to query for all the authors of book #1. Naturally this is very simple to do, but every way I think of returns two rows, whereas what I am looking for is to get this information in a single row. Can this be done?

Thanks for any help, Jonck Received on Sun Feb 01 2004 - 17:30:41 CST