[Solved] SQL Select from table where joined values from a second table are a subset of values from a third table

by


This is a classic Relational Division With Remainder question.

You just need to frame it right:

  • You want all Tasks…
  • … whose TaskTags divide the set of all UserTags for a given User
  • There can be a remainder of UserTags but not a remainder of TaskTags so the former is the dividend, the latter is the divisor.

A typical solution (there are many) is to left join the dividend to the divisor, group it up, then ensure that the number of matched dividends is the same as the number of divisors. In other words, all divisors have a match.

Since you only seem to want the Tasks but not their TaskTags, you can do all this in an EXISTS subquery:

DECLARE @userId int = 1;

SELECT *
FROM Tasks t
WHERE EXISTS (SELECT 1
    FROM TaskTags tt
    LEFT JOIN UserTags ut ON ut.TagId = tt.TagId
        AND ut.UserId = @userId
    WHERE tt.TaskId = t.TaskId
    HAVING COUNT(*) = COUNT(ut.UserId)
);

db<>fiddle

1

solved SQL Select from table where joined values from a second table are a subset of values from a third table