CODEWITHSUNDEEP
sqloracle-database
Bartosz Cieszewski
Bartosz Cieszewski

Reputation: 373

Many to Many joins Oracle DB

I have a question about many to many relationships in Oracle, and a way to join them in a query.

I've pasted my (textbook) example along with some inserts to make it clear. The query I want to build needs to extract books along with their respective authors and other authors that have no book yet.

I've been working with:

SELECT a.name, a.surname, b.book_title
FROM books b, authors a, publish p
WHERE b.book_id = p.book_id(+) AND a.author_id = p.author_id;

However, that fails to do the trick.

Upvotes: 2

Views: 1457

Answers (2)

user10553012
user10553012

Reputation: 19

Book
BookId PK
BookName

Author
AuthorId PK
AuthorName

BookAuthor
BookId PK, FK
AuthorId PK, FK

select b.BookName, a.AuthorName
from BookAuthor ba
inner join Book b on b.BookId = ba.BookId 
inner join Author a on a.AuthorId = ba.AuthorId
where b.BookId =: bookId;

Upvotes: 0

Francois Bourgeois
Francois Bourgeois

Reputation: 3690

SELECT a.name, a.surname, b.book_title
FROM authors a
LEFT OUTER JOIN publish p ON a.author_id = p.author_id
LEFT OUTER JOIN books b ON b.book_id = p.book_id;

The trick is to use an OUTER JOIN, if you like to get the authors without books, too.

Upvotes: 1

Related Questions