Creating join statements

I have the following situation. We sell books on our website, and some
of the books have more than one author. So I needed to create a
many-to-many table, which is the intermidiate table between the author
table and the book table.

I can't get the right join statement to work. I've used the code below,
submitting an isbn (book id number) to identify the book, but the return
from the query simply sends me back all of the authors that are in the
many_to_many table
(called the book_to_author table here). I'd like it to return only the
authors attached to that isbn, instead of all the authors that are in
that table. What's wrong with the code below? Thanks for your help!

SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
Expr3 FROM author a INNER JOIN book_to_author m ON
a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
bf.isbn='"&isbn&"' order by m.id desc

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Do the following:
Your inner join statement should be:INNER JOIN book_to_author m ON
a.author_id= book_to_author.author_id

--
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector -
www.ciquery.com/tools/sqldirector/

"Bill" <BillZimmerman@.gospellight.com> wrote in message
news:3f7b2243$0$24217$75868355@.news.frii.net...
> I have the following situation. We sell books on our website, and some
> of the books have more than one author. So I needed to create a
> many-to-many table, which is the intermidiate table between the author
> table and the book table.
> I can't get the right join statement to work. I've used the code below,
> submitting an isbn (book id number) to identify the book, but the return
> from the query simply sends me back all of the authors that are in the
> many_to_many table
> (called the book_to_author table here). I'd like it to return only the
> authors attached to that isbn, instead of all the authors that are in
> that table. What's wrong with the code below? Thanks for your help!
> SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
> bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
> Expr3 FROM author a INNER JOIN book_to_author m ON
> a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
> bf.isbn='"&isbn&"' order by m.id desc
> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Bill (BillZimmerman@.gospellight.com) writes:
> I have the following situation. We sell books on our website, and some
> of the books have more than one author. So I needed to create a
> many-to-many table, which is the intermidiate table between the author
> table and the book table.
> I can't get the right join statement to work. I've used the code below,
> submitting an isbn (book id number) to identify the book, but the return
> from the query simply sends me back all of the authors that are in the
> many_to_many table
> (called the book_to_author table here). I'd like it to return only the
> authors attached to that isbn, instead of all the authors that are in
> that table. What's wrong with the code below? Thanks for your help!
> SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
> bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
> Expr3 FROM author a INNER JOIN book_to_author m ON
> a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
> bf.isbn='"&isbn&"' order by m.id desc

That CROSS JOIN is most certainly not what you like. (author JOIN
book_to_author) gives your all authors and the books they have
written. Then you form a cartesian product with the book_detail_final
table, and finally you restrict the book with that isbn. But there
is no connection with the first two tables.

It is possible that this query cuts it:

SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id,
m.author_id AS Expr3
FROM author a
JOIN book_to_author m ON a.author_id = m.author_id
JOIN book_detail_final bf ON bf.isbn = m.isbn
WHERE bf.isbn='"&isbn&"'
ORDER BY m.id DESC

But since I don't know your tables, this is a guess.

If the guess is wrong, please post the following:

o CREATE TABLE statements of your tables.
o INSERT statements with sample data.
o The desired given from the sample data.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp