CODEWITHSUNDEEP
mysqldatabasemany-to-many
Madison Scodelario
Madison Scodelario

Reputation: 97

Fetching data from many-to-many relationships

I want to fetch the title from book table and the subject name from subject table for all the books of a certain author

Here is the tables relationships

enter image description here

enter image description here

I have tried to select the title and the subject from the tables but I can't get it to work with the many to many relationship

SELECT book_title,subject_name FROM book,subject WHERE $subject_ID = subject_ID INNER JOIN book_author ON author_ID = '$author_ID'

I thought about making two separate queries, if someone could help me on that I would be really glad.

Upvotes: 3

Views: 7108

Answers (1)

Olivier Coilland
Olivier Coilland

Reputation: 3096

Try this one:

SELECT book_title, subject_name
FROM Book
INNER JOIN Book_Author ON Book.book_ISBN = Book_Author.book_ISBN
INNER JOIN Author ON Book_Author.author_ID = Author.author_ID
INNER JOIN Subject ON Subject.subject_ID = Book.subject_ID
WHERE author_lastname = [whatever];

And nice job posting the model :)

Edit to match the exact need:

SELECT book_title, subject_name
FROM Book
INNER JOIN Book_Author ON Book.book_ISBN = Book_Author.book_ISBN
INNER JOIN Subject ON Subject.subject_ID = Book.subject_ID
WHERE author_ID = '11';

And by the way, you're having an "Column 'author_ID' in where clause is ambiguous" because this column appears both in Book_Author and Author. That's why you have to prefix it with the table name :)

Upvotes: 7

Related Questions