CODEWITHSUNDEEP
mysql
Tenzin Chemi
Tenzin Chemi

Reputation: 5891

I want to join five tables in MySQL

Books

author

publisher

bookauthors

bookpublisher

I want to join all five tables and get these column values:

I should get only one row for a query to get above result. What I wrote was: 

select books.*, authors.authorname
from authors,books
inner join bookauthors on books.bookid = bookauthors.bookid
inner join publisherbook on books.bookid = publisherbook.bookid
where books.bookid = 459567;

but instead I am getting many rows with duplicate data.

Upvotes: 3

Views: 59

Answers (1)

Phil
Phil

Reputation: 165065

You need to use joins for all the tables. Using multiple tables in the FROM clause results in a Cartesian product. Try this

SELECT books.bookid, books.title, books.price, authors.authorname
FROM books
INNER JOIN bookauthors ON books.bookid = bookauthors.bookid
INNER JOIN publisherbook ON books.bookid = publisherbook.bookid
INNER JOIN authors ON bookauthors.authorid = authors.authorid -- assuming you meant authorid and not authored
WHERE books.bookid = 459567;

Of course, if your book has multiple authors, you will get multiple rows in your resultset.

I'm not sure why you're joining in publisherbook (or bookpublisher from your original list) unless you're just ensuring that your book has a publisher. Again, multiple publishers will result in even more (duplicate) results.

If you just want to make sure your book has at least one publisher, try this instead...

SELECT books.bookid, books.title, books.price, authors.authorname
FROM books
INNER JOIN bookauthors ON books.bookid = bookauthors.bookid
INNER JOIN authors ON bookauthors.authorid = authors.authorid -- assuming you meant authorid and not authored
WHERE EXISTS (
    SELECT 1 FROM publisherbook
    WHERE publisherbook.bookid = books.bookid
) AND books.bookid = 459567;

Upvotes: 3

Related Questions