SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[Tasks](
    [TaskId] [int] NOT NULL PRIMARY KEY,
    [TaskName] [nvarchar](MAX) NOT NULL
)

CREATE TABLE [dbo].[Users](
    [UserId] [int] NOT NULL PRIMARY KEY,
    [UserName] [nvarchar](MAX) NOT NULL
)

CREATE TABLE [dbo].[Tags](
    [TagId] [int] NOT NULL PRIMARY KEY,
    [TagName] [nvarchar](MAX) NOT NULL
)

CREATE TABLE [dbo].[TaskTags](
    [TaskId] [int] NOT NULL,
    [TagId] [int] NOT NULL
)

CREATE TABLE [dbo].[UserTags](
    [UserId] [int] NOT NULL,
    [TagId] [int] NOT NULL
)
 
INSERT INTO Tasks VALUES (1,'Task for all SWEs');
INSERT INTO Tasks VALUES (2,'Task for USA SWEs');
INSERT INTO Tasks VALUES (3,'Task for all PMs');
INSERT INTO Tasks VALUES (4,'Task for Europe PMs');

INSERT INTO Users VALUES (1,'Europe SWE');
INSERT INTO Users VALUES (2,'USA SWE');
INSERT INTO Users VALUES (3,'Europe PM');
INSERT INTO Users VALUES (4,'USA PM');

INSERT INTO Tags VALUES (1,'swe');
INSERT INTO Tags VALUES (2,'pm');
INSERT INTO Tags VALUES (3,'usa');
INSERT INTO Tags VALUES (4,'europe');

INSERT INTO TaskTags VALUES (1,1);
INSERT INTO TaskTags VALUES (2,1);
INSERT INTO TaskTags VALUES (2,3);
INSERT INTO TaskTags VALUES (3,2);
INSERT INTO TaskTags VALUES (4,2);
INSERT INTO TaskTags VALUES (4,4);

INSERT INTO UserTags VALUES (1,1);
INSERT INTO UserTags VALUES (1,4);
INSERT INTO UserTags VALUES (2,1);
INSERT INTO UserTags VALUES (2,3);
INSERT INTO UserTags VALUES (3,2);
INSERT INTO UserTags VALUES (3,4);
INSERT INTO UserTags VALUES (4,2);
INSERT INTO UserTags VALUES (4,3);
26 rows affected

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)
);


TaskId
TaskName


1
Task for all SWEs


Warning: Null value is eliminated by an aggregate or other SET operation.


DECLARE @userId int = 2;

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)
);


TaskId
TaskName


1
Task for all SWEs

2
Task for USA SWEs


Warning: Null value is eliminated by an aggregate or other SET operation.