How to filter results using relational division in a dynamic search query?

Using following query i'm going to filter results based on selected tags or categories:

DECLARE @categories NVARCHAR(MAX),
        @tags NVARCHAR(MAX);

SELECT @categories = '1,2,4',  -- comma separated category ids
       @tags = '2,3'           -- comma separated tag ids

SELECT p.id,
       p.title,
       p.price
FROM tbl_products p
  LEFT JOIN tbl_product_categories pc ON @categories IS NOT NULL AND pc.product_FK = p.id
  LEFT JOIN tbl_product_tags pt ON @tags IS NOT NULL AND pt.product_FK = p.id
WHERE ( p.price >= @min_price OR @min_price IS NULL )
  AND ( p.price <= @max_price OR @max_price IS NULL )
  AND ( pc.category_FK IN (SELECT value FROM STRING_SPLIT(@categories, ',')) OR @categories IS NULL )
  AND ( pt.tag_FK IN (SELECT value FROM STRING_SPLIT(@tags, ',')) OR @tags IS NULL)
GROUP BY p.id
HAVING COUNT(p.id) = ( (SELECT COUNT(*) FROM STRING_SPLIT(@categories, ',')) + (SELECT COUNT(*) FROM STRING_SPLIT(@tags, ',')) )

But it does not produce expected results! I am suspicious that HAVING part does not employed correctly as it does not produce right count every time based on passed tags and category ids.

Does anyone know how we could implement such situations, apply relational division to extract products which have all these passed @categories and @tags in common??? Any better way?

-- update: for example use the following sample date:

tbl_products:
id  title     price
===================
1   mouse       10
2   keyboard    18
3   iphone 8    100
4   note 8      90

tbl_product_categories:
product_FK category_FK
======================
1           1
2           1
3           2
4           2

tbl_product_tags:
product_FK tag_FK
=================
1           1
3           1
3           2
4           2

so if we pass @categories = '2' and @tags = '1,2' and min_price = 50 then we should get an iphone 8

Solution

Instead of trying to add counts from your variables, you can use count(distinct [tags|categories]) equals the count for the respective parameter like so:

declare @categories nvarchar(max), @tags nvarchar(max), @min_price int, @max_price int;
select 
    @categories = '2'  -- comma separated category ids
  , @tags = '1,2'      -- comma separated tag ids
  , @min_price = 0
  , @max_price = power(2,30)

select
    p.id
  , p.title
  , p.price
from tbl_products p
  left join tbl_product_categories pc 
    on @categories is not null and pc.product_fk = p.id
  left join tbl_product_tags pt 
    on @tags is not null and pt.product_fk = p.id
where ( p.price >= @min_price or @min_price is null )
  and ( p.price <= @max_price or @max_price is null )
  and ( pc.category_fk in (select value from string_split(@categories, ',')) or @categories is null )
  and ( pt.tag_fk in (select value from string_split(@tags, ',')) or @tags is null)
group by p.id, p.title, p.price
having (count(distinct pc.category_fk) = (select count(*) from string_split(@categories, ',')) or @categories is null) 
   and (count(distinct pt.tag_fk) = (select count(*) from string_split(@tags, ',')) or @tags is null)

demo: dbfiddle.uk demo

returns:

+----+----------+-------+
| id |  title   | price |
+----+----------+-------+
|  3 | iphone 8 |   100 |
+----+----------+-------+

When it comes to performance, you will benefit from rewriting this as a procedure with dynamic sql execution, or at least option (recompile) as shown in these references:

Here is an example of a dynamic sql search procedure for your query that uses exists ...having count()... instead of left join... where... having count(distinct ...) that simplifies the plan a bit (plan comparison demo):

create procedure product_search (
    @categories nvarchar(max)
  , @tags nvarchar(max)
  , @min_price int
  , @max_price int
) as 
begin;
set nocount, xact_abort on;
declare @sql nvarchar(max);
declare @params nvarchar(256);
set @params = '@categories nvarchar(max), @tags nvarchar(max), @min_price int, @max_price int';
set @sql = ';
select
    p.id
  , p.title
  , p.price
from tbl_products p
where 1=1'
if @min_price is not null
set @sql = @sql + '
  and p.price >= @min_price';
if @max_price is not null
set @sql = @sql + '
  and p.price <= @max_price';
if @categories is not null 
set @sql = @sql + '
  and exists (
      select 1 
      from tbl_product_categories ic
      where ic.product_fk = p.id
        and ic.category_fk in (select value from string_split(@categories, '',''))
      having count(*) = (select count(*) from string_split(@categories, '',''))
      )';
if @tags is not null 
set @sql = @sql + '
  and exists (
      select 1 
      from tbl_product_tags it
      where it.product_fk = p.id
        and it.tag_fk in (select value from string_split(@tags, '',''))
      having count(*) = (select count(*) from string_split(@tags, '',''))
      )';

exec sp_executesql @sql, @params, @categories, @tags, @min_price, @max_price;
end;

executed like so:

declare @categories nvarchar(max), @tags nvarchar(max), @min_price int, @max_price int;
select 
    @categories = null  -- comma separated category ids
  , @tags = '1,2'      -- comma separated tag ids
  , @min_price = null
  , @max_price = power(2,30)

exec product_search @categories, @tags, @min_price, @max_price

demo: dbfiddle.uk demo

returns:

+----+----------+-------+
| id |  title   | price |
+----+----------+-------+
|  3 | iphone 8 |   100 |
+----+----------+-------+