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!
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 ;
MS-SQL
's CHARINDEX function requires VARCHARs or NVARCHARs as parameters, so your categories.id should be converted to VARCHAR. Something like this:
CHARINDEX(CAST(categories.id AS VARCHAR(16)), books.multiple_category_id ) > 0 )
However, I would consider refactoring both tables and your query:
1. Query refactoring
As it is written, you can have false matches. E.g. categories.id = 1
and your multiple_category_id
is '11,12'
. An alternative is to split your string is to have your condition like this:
AND EXISTS (SELECT 1 FROM dbo.SplitString(books.multiple_category_id) WHERE Token = categories.id)
Also, * should be replaced with actually needed columns (in this case, it will bring all the columns in both tables). Something like this:
2. Table refactoring
Instead of parsing strings you should define an X table like this:
categoryId FK references category bookId FK references book PRIMARY KEY (categoryId, bookId)
so, that you can do your query using simple JOIN
s instead of strings searches which can be slower (no indexes can be used).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With