MySQL - please help fix a mistake in the query

Question

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:

book id
book title,
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:

specify the exact columns I need in my view:

SELECT b.id, b.title, a.name FROM books as b

join the pivoting table on book_id:

INNER JOIN authors_books as ab on b.id = ab.book_id

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?

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. — Shadow, Sep 16 '19 at 13:01
Your codes look nice, Can you get your expected result with a single query? Can you show us samples. — Mert Akkanat, Sep 16 '19 at 13:03
@Shadow Omg, guys you are right, I have a problem with one of my seeders. On the other side, in the second clause I should have used LEFT JOIN instead of inner — Cheshire Katzzze, Sep 16 '19 at 13:39
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. — Pepper, Sep 16 '19 at 13:43
@Shadow When I use LEFT JOIN in the second statement instead of INNER, I get +3 results. Why is it like that? — Cheshire Katzzze, Sep 16 '19 at 13:43
@Pepper I came to same solution in the end, and indeed it has proven to be the right one :) Please post your answer separately so that I can mark it as the correct one — Cheshire Katzzze, Sep 16 '19 at 13:44
You should use `LEFT JOIN` instead of both `INNER JOIN` in my opinion, in case either your pivot (authors_books) references an unknown author, or does not reference any author at all (no entries for a specific book) — Pepper, Sep 16 '19 at 13:45
@Pepper This is correct. Please post your answer separately. — Cheshire Katzzze, Sep 16 '19 at 13:47
glad that someone undestand me sometimes :D Have a good work. — BUcorp, Sep 16 '19 at 13:58
My apologies to @BUcorp, I just realised `LEFT JOIN` and `LEFT OUTER JOIN` do exactly the same thing.... Could you please accept his answer instead, @Cheshire Katzzze? — Pepper, Sep 16 '19 at 14:10
@Pepper don't worry , it's enough for me that you are honest and competent with stack community. I don't like to read too much so usually i try to resume all information in short description. The explanation is the second step i think. — BUcorp, Sep 16 '19 at 14:19

BUcorp · Accepted Answer · 2019-09-16T13:09:44.023

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

edited Sep 16 '19 at 13:09

answered Sep 16 '19 at 13:03

BUcorp

350
2
12

Outer join is not what I need here. Though indeed, I should have changed the second JOIN statement to LEFT – Cheshire Katzzze Sep 16 '19 at 13:41
1

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. – BUcorp Sep 16 '19 at 13:52

score 1 · Answer 2 · answered Sep 16 '19 at 13:52

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

MySQL - please help fix a mistake in the query

2 Answers2