Select all from two many-to-many related tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select all from two many-to-many related tables

Author

Topic

SunnyDay
Starting Member

9 Posts

Posted - 2007-10-11 : 17:11:45

Hello, everyone!

Please give your ideas about how to create a query from two tables, which have many-to-many relationship. For example, we have table Book and table Author and consider, that one author had wroten several books and one book had being wroten by several authors. So we have many-to-many relationship of these two tables.
Usually this sutuation resolves by maens of creating the third table AuthorBook, which consist of key fields from the Author table and key fields from the Book table. So that the Author table is the parent table to AuthorBook, and the Book table is the parent table to AuthorBook. So we have two relations one-to-many. Now the question is how to get all related values from both tables, using JOIN operation and the conjunctive table?

I would greatly appreciate any answers

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-11 : 17:19:18

What have you come up with so far? Post what you have so far, even if it doesn't work at all, and then we can help you get it right.

Jim

SunnyDay
Starting Member

9 Posts

Posted - 2007-10-12 : 03:45:46

OK. You are right. It would be simpler.

SELECT AuthorName, BookTitle
FROM Author
INNER JOIN AuthorBook
ON Author.AuthorID = AuthorBook.AuthorID,
Book
INNER JOIN AuthorBook
ON Book.BookID = AuthorBook.BookID

Kristen
Test

22859 Posts

Posted - 2007-10-12 : 03:50:01

[code]
SELECT AuthorName, BookTitle
FROM Author
INNER JOIN AuthorBook
ON Author.AuthorID = AuthorBook.AuthorID
~~Book~~
INNER JOIN ~~AuthorBook~~ Book
ON Book.BookID = AuthorBook.BookID
[/code]
Kristen

SunnyDay
Starting Member

9 Posts

Posted - 2007-10-12 : 04:49:41

Kristen, I appreciate you very much!!! It's exactlly I wanted to get!

Kristen
Test

22859 Posts

Posted - 2007-10-12 : 05:10:38

No problem! Welcome to SQL Team ...

renu
Starting Member

47 Posts

Posted - 2007-10-12 : 07:43:02

select a.au_fname,t.title
from authors a inner join titleauthor ta
on a.au_id=ta.au_id
inner join titles t
on ta.title_id = t.title_id