即使在变量列表中的列中至少有一个值匹配时，Sql查询也可以选择行计数

Question

I have a table name called Kits in which has 5 columns for Book ISBN.

• So i want to select Row even when at least 1 value of ISBN gets matched with any of the 5 columns(ISBN_BOOK1,ISBN_BOOK2,ISBN_BOOK3,ISBN_BOOK4,ISBN_BOOK5) of table kits meant for Storing ISBN.
• if any one of the ISBN exists then return count.

I have written an sql query for this but I am getting row output only if last value is matching with any row. In my case the value is 7589765432 Here is my sql query:

Select * 
  from kits k
  left 
  join kits_cstm kc
    on k.id = kc.id_c 
 where '8192933563' 
   and '8192933567' 
   and '8192933568' 
   and '8192933564' 
   and '7589765432' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c) 
   and kc.city_c = 'BAN' 
   and kc.kit_for_c = 'SCHL' 
   and k.deleted = 0 
 ORDER 
    BY k.kit_id DESC 

Answer 1

You can't use "AND" to conjoin values to one operator

WRONG WAY: WHERE '8192933536' AND '8192933567' IN ({array]) RIGHT WAY: WHERE '8192933536' IN ({array]) AND '8192933567' IN ({array])

But I suspect that what you're actually trying to do is more like:

SELECT 
      * 
FROM 
     kits k LEFT JOIN kits_cstm kc
          ON k.id = kc.id_c 
WHERE 
     ('8192933563' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c)
      OR 
     '8192933567' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c)
      OR 
     '8192933568' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c)
      OR 
     '8192933564' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c)
      OR 
     '7589765432' IN (kc.isbn_book_1_c,kc.isbn_book_2_c,kc.isbn_book_3_c,kc.isbn_book_4_c,kc.isbn_book_5_c) 
      )
AND
     kc.city_c = 'BAN' 
AND
     kc.kit_for_c = 'SCHL' 
AND
     k.deleted = 0 
ORDER BY 
     k.kit_id DESC