Question 20590 in Mysql Question posted in
The official MySQL documentation can be found here.

Why SQL Query with LEFT JOIN is giving this result? – Mysql

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!

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search