Geek asks and answers

I want to join five tables in MySQL

Books

  • bookid (PK)
  • title
  • price

Author

  • author (PK)
  • AUTHORNAME

publisher

  • pubid (PK)
  • pubname

Bookauthors

  • bookid (FK)
  • AuthorID

bookpublisher

  • bookid (FK)
  • PubID

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

  • Bookid
  • title
  • price
  • AUTHORNAME
  • pubname

I need to get only one row for the query in order to get the result. I wrote:

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 get a lot of rows with duplicate data.

+3
source share
1 answer

You need to use joins for all tables . Using multiple tables in a sentence FROMresults in a Cartesian product. try it

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;

, , .

, publisherbook ( bookpublisher ), , . , () .

, , ...

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;
+3

More articles:


All Articles