Reputation: 5891
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
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