How do I use my join tables MySQL?

Question

I have 3 tables in a MySQL database,

Author
Book
Author_has_Book.

The author has two columns
idauthor
name

Book also has two columns
idbook
name

Author_has_Book also has two columns
foreign keys of an author and a book book_idbook
author_idauthor.

I have successfully inserted an author and a book into both tables and I have entered their keys into the join table.

Now how do I use this join table to get all books by a certain author, or all authors for a book? Is this accomplished with joins?

edit: The suggested duplicate is not the same question.

possible duplicate of [Explanation of self-joins](http://stackoverflow.com/questions/2458519/explanation-of-self-joins) — Shawn, Jul 08 '15 at 04:28
possible duplicate of [how to query many-to-many?](http://stackoverflow.com/questions/832584/how-to-query-many-to-many) — kittykittybangbang, Jul 08 '15 at 04:33

score 1 · Answer 1 · answered Jul 08 '15 at 04:28

It will get you all records

SELECT Author.*
FROM Author Author 
INNER JOIN Author_has_Book AuthorHasBook
    ON Author.idauthor = AuthorHasBook.author_idauthor
INNER JOIN Book Book
    ON AuthorHasBook.book_idbook = Book.idbook

and if you wish to select any specific author, you just need to mention it in where clause, see example below:

SELECT Author.*
FROM Author Author 
INNER JOIN Author_has_Book AuthorHasBook
    ON Author.idauthor = AuthorHasBook.author_idauthor
INNER JOIN Book Book
    ON AuthorHasBook.book_idbook = Book.idbook
WHERE Author.idauthor = 'your author id'

score 0 · Answer 2 · answered Jul 08 '15 at 04:28

0

Try this query

select a.name,c.name from book a
join Author_has_Book b on a.idbook=b.idbook
join author c on b.idauthor=c.idauthor

answered Jul 08 '15 at 04:28

Mukesh Kalgude

4,814
2
17
32

How do I use my join tables MySQL?

2 Answers2