Many-to-many simple SELECT [newbie] question

Hi,

I'm starting to learn SQL and trying to use it with PHP. I'm stuck on a
textbook example of many to many relation. I have a Books database.
There are numerous authors, many books, not all books having authors at
all and some authors with many books to their name.

I want to be able to list complete book data contained in two tables. In
case of several authors there should be a new 'compund-collective
author' but I don't know how to construct a query to achieve it.

Books
-----
book_id
book_title
..
..

Authors
-------
aut_id
aut_sname
..
..

I started with the following query:

SELECT
Books_authors.aut_id
, Books_Authors.book_id
, Books.book_title
, Authors.aut_sname
, Authors.aut_id

from Books_Authors
left JOIN Books ON (Books.book_id = Books_Authors.book_id)
LEFT JOIN Authors ON (Authors.aut_id = Books_Authors.aut_id)

ORDER BY books.book_id


Could somebody give me a push in the right direction?

Cheers
--
Andrzej

On Fri, 11 Jan 2008 23:16:59 +0100, Andrzej kK
<kroperk-avis-cuthisout@cuthisout.wp.pl> wrote:
> I want to be able to list complete book data contained in two tables. In
> case of several authors there should be a new 'compund-collective
> author' but I don't know how to construct a query to achieve it.

> SELECT
> Books_authors.aut_id
> , Books_Authors.book_id
> , Books.book_title
> , Authors.aut_sname
> , Authors.aut_id
>
> from Books_Authors
> left JOIN Books ON (Books.book_id = Books_Authors.book_id)
> LEFT JOIN Authors ON (Authors.aut_id = Books_Authors.aut_id)
>
> ORDER BY books.book_id
>
>
> Could somebody give me a push in the right direction?

GROUP_CONCAT()
--
Rik Wasmus