1

I have three tables, many-to-many relation:

  • BOOKS

     CREATE TABLE `books` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `title` VARCHAR(100) NOT NULL, `condition` ENUM('mint', 'new', 'medium', 'poor', 'needs replacement'), `date_added` DATE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

  • AUTHORS

     CREATE TABLE `authors` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(50) NOT NULL, `pseudonim` VARCHAR(50) NOT NULL, `year_of_birth` INT(4) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

  • AUTHORS_BOOKS table:

     CREATE TABLE `authors_books` ( `author_id` INT(11) UNSIGNED NOT NULL, `book_id` INT(11) UNSIGNED NOT NULL, PRIMARY KEY (`author_id`, `book_id`), CONSTRAINT `fk1_authors_authors_id` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT `fk2_books_book_id` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What I am trying to make is to fetch data containing the following info:

  1. book id
  2. book title,
  3. author's name

My query does not return the relevant data, I probably have missed the correct join somewhere, but as I see, the biggest problem is in my understanding. I would appreciate if anyone could check the consequence of things to be done as I see it:

  1. specify the exact columns I need in my view:
SELECT b.id, b.title, a.name FROM books as b
  1. join the pivoting table on book_id:
INNER JOIN authors_books as ab on b.id = ab.book_id
  1. Since I need the names of the authors, I am making one more join to bring into the game the authors table:
INNER JOIN authors AS a ON ab.authors_id = a.id

So in the end I have the following query

SELECT b.id, b.title, a.name FROM books as b INNER JOIN authors_books as ab on b.id = ab.book_id INNER JOIN authors AS a ON ab.authors_id = a.id

that returns me an incomplete list of books skipping most of them. I can't get how does it happen. Where is the mistake I am making, what concept am I missing?

Improve this question
14
  • 3
    I don't think there is anything wrong with the query. Can you pls show us some sample data, the result you are getting and the result you are expecting. Commented Sep 16, 2019 at 13:01
  • 1
    Yeah - would need to see sample data. Commented Sep 16, 2019 at 13:01
  • 2
    Your codes look nice, Can you get your expected result with a single query? Can you show us samples. Commented Sep 16, 2019 at 13:03
  • 1
    Your code is correct, the problem comes from your data, it will only return books for which it does find an author (using the authors_books pivot table). One way to fix this would be using left joins (to return a.name = null when no author is found). Also, if books can have multiple authors, which your tables can allow, the book will show up several times in your results. Commented Sep 16, 2019 at 13:43
  • 1
    glad that someone undestand me sometimes :D Have a good work. Commented Sep 16, 2019 at 13:58

2 Answers 2

Reset to default
2

try

SELECT b.id, b.title, a.name FROM books as b LEFT OUTER JOIN authors_books as ab on b.id = ab.book_id LEFT OUTER JOIN authors AS a ON ab.authors_id = a.i

because you are missing some rows i think you should use left outer join so you'll have the full result against the main table books

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Outer join is not what I need here. Though indeed, I should have changed the second JOIN statement to LEFT
I understand but does the query extract all the missing rows you mention before? If so i'll reccomend you to just change a little this query adding where ab.book_id is not null for example.
1

As mentioned by @BUcorp, the main issue comes from missing rows.

To solve this you can use LEFT JOIN instead of both your INNER JOIN to ensure all books are present in your results, even if they have no author or an unknown one (as in, with an ID not present in authors):

SELECT b.id, b.title, a.name FROM books as b LEFT JOIN authors_books as ab on b.id = ab.book_id LEFT JOIN authors AS a ON ab.authors_id = a.i

Finally, to avoid getting multiple entries in case a book has several authors (if that could happen in your situation), you could for example use GROUP_CONCAT(a.name) AS authors and GROUP BY b.id

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.