This question has already been answered:

There are 2 tables User, Book, one-to-many relationship, that is, the user can have many books.

* Book

  • user_id
  • book_id

From the structure of the table it can be seen that the relationship is of the type many_many, but this is not so important. For example, I need to select users who have read books with ID 1 and 2.

Такой запрос не работает "SELECT * FROM user LEFT JOIN book ON user.id = book.user_id WHERE book.book_id = 1 AND book.book_id = 2 ";

Reported as a duplicate by members Mike , Bald , aleksandr barakin , lexxl , user194374 August 4 '16 at 7:54 .

A similar question was asked earlier and an answer has already been received. If the answers provided are not exhaustive, please ask a new question .

  • And what exactly does not work? - Jeid
  • LEFT is not needed. It would be necessary if you were interested in all users, and some would also have to show the required books. And the condition book.book_id = 1 AND book.book_id = 2 always false. One entry cannot have id=1 И-одновременно id=2 . It would be correct to book.book_id = 1 OR book.book_id = 2 , which in a short record looks like book.book_id IN(1,2) - Mike
  • @Mike - book.book_id = 1 AND book.book_id = 2 - I understand that this condition is false, but I don’t know how to compose a query correctly - Roman Maltsev
  • @Mike - It seems to be pretty clear what I want to get as a result: нужно выбрать пользователей которые прочли книги с ИД 1 и 2 . If you put an OR - it will not give the desired result. - Roman Maltsev
  • @Mike, ru.stackoverflow.com/questions/518991/… - what you need - Roman Maltsev

1 answer 1

1) Why do you need a LEFT JOIN?

2) in one line of the selection the condition: book.book_id = 1 AND book.book_id = 2 - will always give a lie, since book_id cannot be equal to 1 and 2 at the same time :) :)

About the following request will help you, for example:

 SELECT user.id FROM user INNER JOIN book ON user.id = book.user_id GROUP BY user.id HAVING MAX(IF(book.book_id = 1, 1, 0)) = 1 AND MAX(IF(book.book_id = 2, 1, 0)) = 1

Both conditions in HAVING mean that there is at least one book_id value in the group that we specified. Those. exactly what you need.

UPD: According to Mike's advice:

 SELECT user.id FROM user INNER JOIN book ON user.id = book.user_id WHERE book.book_i IN (1, 2) GROUP BY user.id HAVING COUNT(*)=2
  • I understand the logic, now I will try to draw up criteria in yii - Roman Maltsev
  • @Mike, can you explain what this expression means count(1)=2 - Roman Maltsev
  • @ RomanMalcev Same as count(*)=2 . Before grouping, the query will select for each user as many entries as there are books with the specified IDs. Since in the search condition there are 2 books, then if the user has 2 books with ID 1 and 2, then 2 records will be received and count (*) will give 2 - Mike
  • @ RomanMalcev, the count (X) function counts the number of X values ​​in a group other than NULL. Thus, COUNT (1), COUNT ('some string const') will return just the number of rows. In the language standard, the designation COUNT (*) is accepted. just the number of rows in a group. - pegoopik