Filter with SQL Server by Group ID

Question

I have two tables and I need to filter the data by filter id depends on the relation to to filter group id.

For example I have this two tables:

Table 1:

ItemID	FilterID
3	122
3	123
3	4
17	123

Table 2:

FilterID	FilterGroupID
122	5
123	5
4	1

• If I search by filter id = 123 than all item id with this filter need to be returned.
• If I search two or more different filter id that have different group id I need to get only the item ids that have all filter id and group id.

Desired output:

• first input: 123 -> return item id =3 and item id = 17
• second input: 123,4 -> return item id = 3 because filter id 123 belong to group id 5 and filter id 4 belong to group id 1 and item id 3 is the only one that has this two filters.
• third input: 122,123 -> return item id =3 and item id = 17 because both filter id belong to same group.

I am getting a little lost with this query and I will be glad to get some help.

I’ll try to simplify it: Let’s say we have group filter of size and group filter of color. If I filter by size S or M than I need to get all items with this sizes. If I want to add color like blue than the answer will cut the result by: item with size S or M and Color blue. So filter from different group may cut some results

Answer 1

It seems that you want to get every ItemID which has at least one matching filter from each FilterGroupID within your filter input. So within each group you have or logic, and between groups you have and logic

If you store your input in a table variable or Table-Valued parameter, then you can just use normal relational division techniques.

This then becomes a question of Relational Division With Remainder, with multiple divisors.

There are many ways to slice this cake. Here is one option

• Join the filter input to the groups, to get each filter's group ID
• Use a combination of DENSE_RANK and MAX to get the total distinct groups (you can't use COUNT(DISTINCT in a window function so we need to hack it)
  • You can change this step to use a subquery instead of window functions. It may be faster or slower
• Join the main table, and filter out any ItemIDs which do not have their total distinct groups the same as the main total

SELECT
  t1.ItemID
FROM (
    SELECT *,
      TotalGroups = MAX(dr) OVER ()
    FROM (
      SELECT
        fi.FilterID,
        t2.FilterGroupID,
        dr = DENSE_RANK() OVER (ORDER BY t2.FilterGroupID)
      FROM @Filters fi
      JOIN Table2 t2 ON t2.FilterID = fI.FilterID
    ) fi
) fi
JOIN Table1 t1 ON t1.FilterID = fi.FilterID
GROUP BY
  t1.ItemID
HAVING COUNT(DISTINCT FilterGroupID) = MAX(fi.TotalGroups);

db<>fiddle