Using many to many tables -- sql server (alternate)

Bill
8/27/2003 8:53:03 PM

I'm having a problem writing the correct sql to bind togeather three
tables. Here's the problem: I have a table for detail information on
books that are in our catalog, and a table for authors. Unfortunatly, it
is not a one to one relationship. Some books are written by several
authors, hence, I've created an intermidiate table called book_to_author
where I list the isbn's for the books next to the author id's, so I may
list an isbn three times with three different author id's next to it, if
three authors have contributed to it.

Now I have to write a sql statement that will pull up the book detail
info, such as the title, and the three authors associated with it.
Assuming I have a table called authors, with an author name in it, one
called book_to_authors, with the isbn and author_id, and a third called
books, with the title in it, how would I write the sql to join all the
info where the isbn='0530725318?

Thanks a million if you can help. I've been trying to write this all
morning, and can't get it to work.

Bill

*** Sent via Developersdex http://www.developersdex.com ***

John Bell
8/27/2003 10:38:57 PM

Hi

Something like the following should do!!

SELECT B.Title, A.Name
FROM Books B JOIN Books_To_Authors C ON C.ISBN = B.ISDB
JOIN Authors A ON C.AuthorId = A.Authorid

If you are actually wanting a crosstab query here are a list of useful
links:
http://tinyurl.com/i9mt

John

[quoted text, click to view]

sql server (alternate) : Using many to many tables