From: "Parker Shannon" <pshannon@ixpres.com>
Newsgroups: comp.databases.theory
References: <28a69e7c.0402011530.2f493fc1@posting.google.com>
Subject: Re: Many-to-many query question
Date: Mon, 2 Feb 2004 16:02:03 -0500
Lines: 67
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 206.148.128.201
Message-ID: <401ecf4b_1@news.vic.com>
X-Trace: news.vic.com 1075760971 206.148.128.201 (2 Feb 2004 17:29:31 -0500)
X-Authenticated-User: pshannon@ixpres.com
Organization: Newsville.Com (http://www.newsville.com)
Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news.linkpendium.com!news-out.visi.com!petbe.visi.com!news.octanews.net!news.maxwell.syr.edu!wn11feed!worldnet.att.net!204.71.34.3!newsfeed.cwix.com!news.loxinfo.co.th!peony.singnet.com.sg!columbine.singnet.com.sg!crtntx1-snf1.gtei.net!news.gtei.net!news.vic.com!not-for-mail
Xref: newssvr20.news.prodigy.com comp.databases.theory:23722

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@vanderkogel.net> wrote in message
news:28a69e7c.0402011530.2f493fc1@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