Why SQL Query with LEFT JOIN is giving this result?

I have the following tables defined by:

CREATE TABLE db.books (
	id INT auto_increment not NULL,
	title varchar(100) NULL,
	category_id INT NULL,
	PRIMARY  key (id)
)

CREATE TABLE db.books_categories (
	id INT auto_increment not NULL,
	category varchar(100) NULL,
	PRIMARY  key (id)
)

CREATE TABLE db.collection (
	id INT auto_increment not NULL,
	title varchar(100) NULL,
	PRIMARY  key (id)
)

CREATE TABLE db.collections_has_books (
	id INT auto_increment not NULL,
	collection_id int not NULL,
	book_id int not NULL,
	PRIMARY  key (id)
)


ENGINE=InnoDB
DEFAULT CHARSET=latin1
COLLATE=latin1_swedish_ci;


INSERT INTO db.books (title,category_id) VALUES
	 ('Harry Potter and the philosopher''s stone',2),
	 ('The lord of the rings',2),
	 ('Moby dick',3),
	 ('Robinson Crusoe',3),
	 ('The Time Machine',1),
	 ('The Great Gatsby',4),
	 ('The Treasure Island',3);
	 
	 
INSERT INTO db.books_categories (category) VALUES
	 ('science_fiction'),
	 ('fanstasy'),
	 ('adventures'),
	 ('other');
	 
INSERT INTO db.collection (title) VALUES
	 ('books for vacations'),
	 ('books for teenagers'),
	 ('first readings'),
	 ('books about adventrues and other stuff');
	 
INSERT INTO db.collections_has_books (collection_id, book_id) VALUES
	 (1,1),
	 (1,4),
	 (1,5),
	 (2,1),
	 (2,2),
	 (2,4),
	 (3,4),
	 (3,5),
	 (4,4),
	 (4,5),
	 (4,6);
	 

and I want to obtain the all books included in other collections except collection id = 2, for that I write this MySQL request with LEFT JOINS:

SELECT distinct `b`.* FROM `books` `b` 
LEFT JOIN `books_categories` `c` 
ON c.id = b.category_id 
LEFT JOIN `collections_has_books` `chb` 
ON chb.book_id = b.id 
WHERE (chb.collection_id != 2)  

but I get those results:

[id] [title] [category_id]

1 Harry Potter and the philosopher's stone 2 <-- I can not understand why I am receiving this value if I put WHERE (chb.collection_id != 2)
4 Robinson Crusoe 3
5 The Time Machine 1
6 The Great Gatsby 4

As I said, I can not understand why I am getting the book Id = 1 if it is in a collection_id = 2.

Could please tell me someone what I am doing wrong?

Thank you!

If a book can be in multiple collections. You should use NOT EXISTS if you want to exclude a book that is in a specific collection.

select  *
from    books b
where   not exists (
    select  chb.collection_id
    from    collections_has_books chb
    where   chb.book_id = b.id
    and     chb.collection_id = 2
)

This will also include books that are not in any collections so you may need to include an EXISTS/IN as well if you only want books that are assigned to a collection

JamieD77 explained it too.

I think it is more clear, when you execute these sql:

select * 
from books b 
    LEFT JOIN `collections_has_books` `chb` ON chb.book_id = b.id 

</details>