sql - Query based on recorded criteria -

there 2 tables,

 categories(id, name) products(id, text, categoryid) 

there table filtering products:

 filter(categoryids, containtext) 

categoryids in filter table comma-separated: 100,101

we want query products based on criteria extracted filters table.

example: filters have 2 records:

 categoryids   |   containtext ----------------------------- 100,101       |   200,201       |   b 

here products want query:

containting text 'a' in categories 100 or 101 

or

containting text 'b' in categories 200 or 201 

we not use dynamic query.

thanks help.

as per giorgos comment, need normalize filter table. if you're stuck design, here solution:

first, need have string splitter. here 1 taken aaron bertrand's article:

create function dbo.splitstrings_xml (    @list       nvarchar(max),    @delimiter  nvarchar(255) ) returns table schemabinding    return     (         select item = y.i.value('(./text())[1]', 'nvarchar(4000)')              (          select x = convert(xml, '<i>'            + replace(@list, @delimiter, '</i><i>')            + '</i>').query('.')       ) cross apply x.nodes('i') y(i)    ); 

---

then, need split filter table separate rows. joins on products, categories , splitted filter table:

with ctefilter(categoryid, containtext) as(     select         cast(s.item int), f.containtext     filter f     cross apply dbo.splitstrings_xml(f.categoryids, ',') s ) select p.* products p inner join categories c     on c.id = p.categoryid inner join ctefilter f     on f.categoryid = c.id     , p.text '%' + f.containtext + '%'