SQL Argument data type int is invalid for argument 1 of charindex function

I’m trying to convert MySQL query to MsSql query but I’m having trouble. Here is my query:

MySQL

SELECT *,
(SELECT count(books.id)
   FROM books
   WHERE books.status = 1
     AND FIND_IN_SET(categories.id, books.multiple_category_id)) AS book_count
   FROM categories, books
   WHERE categories.parent_id=0
     AND categories.status=1 ;

SQL I’ve tried

SELECT *,
  (SELECT count(books.id)
   FROM books
   WHERE books.status = 1
     AND CHARINDEX(categories.id, books.multiple_category_id) > 0) AS book_count
   FROM categories, books
   WHERE categories.parent_id=0
     AND categories.status=1 ;

The errors I’m getting are:

Argument data type int is invalid for argument 1 of charindex function.

For reference. http://sqlfiddle.com/#!3/4ed19/3

does anyone have any ideas? Thanks in advance!

Answer

Use this query. I just changed categories.id to CAST(categories.id AS VARCHAR). Because the id is integer in categories table.

SELECT *,
(SELECT count(books.id)
   FROM books
   WHERE books.status = 1
     AND CHARINDEX(CAST(categories.id AS VARCHAR), books.multiple_category_id) > 0) AS book_count
FROM categories,
     books
WHERE categories.parent_id=0
  AND categories.status=1 ;