Re: Many-to-many query question

"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?

Yes.

After using the WHERE clause to restrict the book# to 1 you can then do a group by book# that then permits other functions (eg: aggregates) to be utilised.

For example:

select book#, min(author#), max(author#),count(*) "Number of Authors" from joining table
where book# = 1
group by book#

Pete Brown
Falls Creek
NSW
Australia Received on Mon Feb 02 2004 - 01:03:38 CET