Re: Many-to-many query question

Hello Jonck,

The first thing you should do is to get rid of the field "join_id" in the joining table. It is of no value and although the DBMS is smart enough to increment the key, "join_id" you will never use this key to retrieve data. Never.

To answer your question, "what I am looking for is to get this information in
a single row?", No.

If you must show that Edwards and Williams are the co-authors of MySQL on a single line, in your procedure, store the two rows in an array (2,12) or whatever:

MySQL, Edwards
MySQL, Williams

Then, in the procedure, format the result to display "MySQL, Edwards, Williams".

Regards . . .

"Jonck van der Kogel" <jonck_at_vanderkogel.net> wrote in message news:28a69e7c.0402011530.2f493fc1_at_posting.google.com...
> 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 Mon Feb 02 2004 - 22:02:03 CET