Specify joint conditional and excluding columns with WHERE multiple times

Well, I’m using MYSQL and I cannot specify specific columns multiple times. I know that the following code is wrong but it is to better understand what I am trying to do.

Note: id_follow_book = id book

I need to specify in the same query, from id_follow_book select some id in specific and with some specific chapters. With IN it does not help me, because I would select the id to follow the books well, but then I would not pick the correct chapters for each follow-up. Exist the possibility that the user is not following a book or id_follow_book, maybe even none. What I’m trying to return those that are followed by the user.

SELECT id_follow_book, num_chapt
FROM Follow, Chapters
WHERE id_user = 1 AND
id_follow_book = id_chapter_book AND
(id_follow_book = 15 AND num_chapt = (10+1)) AND/OR
(id_follow_book = 25 AND num_chapt = (5+1)) AND/OR
(id_follow_book = 30 AND num_chapt = (23+1))

The sum numbers are variables.
I tried too with CASE WHEN, but I didn’t achieve it. Thanks and sorry for my english.

Follow                                      Chapters

id_follow_book   id_user                 id_chapter_book   num_chapt
    30                1                       30              6
    25                1                       30              5
    13                1                       30              4
    21                1                       25             24
                                              25             23

Expected result:

id_follow_book    num_chapt
    30                  6
    25                 24

I’m confused. What your “where” clause would return and what your “expected results” show are two completely different things.

According to your WHERE clause, you should only see chapter 11 for book 15, chapter 6 for 25 and chapter 24 for book 30. The parenthesis group conditions together. The AND/OR is not possible (though, I’m not sure what you’re trying to accomplish)

If you’re trying to get the books that the user is following, you just have to join them together.

SELECT id_follow_book
     , num_chapt
  FROM follows
 INNER JOIN chapters ON id_follow_book = id_chapter_book

If you’re looking for a specific set of books, include the ID in the where clause.

SELECT id_follow_book
     , num_chapt
  FROM follows
 INNER JOIN chapters ON id_follow_book = id_chapter_book
 WHERE id_follow_book IN (15, 25, 30)

If you’re looking for the last num_chapt for a specific book, you could use GROUP BY and a MAX() call

SELECT id_follow_book
     , MAX(num_chapt) AS num_chapt
  FROM follows
 INNER JOIN chapters ON id_follow_book = id_chapter_book
 WHERE id_follow_book IN (15, 25, 30)
 GROUP BY id_follow_book

I need to be able to specify the chapter number for each book. If I use MAX () it will only return the biggest one that is stored in the database, not the one I need. Maybe it’s a bad approach to say it with WHERE but it’s simply to understand what I’m trying to do.

Then you’re going to have to include them in your where clause. You would need to surround the book/chapter portion in parenthesis. A sample to get your “expected results” would be below. I’ve tried to do some additional parenthesis formatting to make it more apparent

SELECT id_follow_book
     , num_chapt
  FROM follows
 INNER JOIN chapters ON id_follow_book = id_chapter_book
 WHERE id_user = 1
   AND (
        (id_follow_book = 30 AND num_chapt = 6) OR
        (id_follow_book = 25 AND num_chapt = 24)
       )
1 Like

yeah perfect! that was what i need ty!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.