SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ContentTag([Id] int, [ContentId] int, [TagId] int);
    
INSERT INTO ContentTag([Id], [ContentId], [TagId]) VALUES
    (1, 48, 3),
    (2, 48, 4),
    (3, 48, 5),
    (4, 48, 6),
    (5, 47, 7),
    (6, 47, 8),
    (9, 47, 1),
    (10, 47, 2);
    
SELECT * FROM ContentTag;    


Id
ContentId
TagId


1
48
3

2
48
4

3
48
5

4
48
6

5
47
7

6
47
8

9
47
1

10
47
2


CREATE TABLE FolderTag([Id] int, [FolderId] int, [TagId] int);
    
INSERT INTO FolderTag([Id], [FolderId], [TagId]) VALUES
    (2, 2, 3),
    (3, 2, 4),
    (4, 2, 5),
    (5, 2, 6),
    (6, 4, 3),
    (7, 5, 1),
    (8, 5, 2),
    (9, 5, 3),
    (10, 10, 1);
    
SELECT * FROM FolderTag;        


Id
FolderId
TagId


2
2
3

3
2
4

4
2
5

5
2
6

6
4
3

7
5
1

8
5
2

9
5
3

10
10
1


WITH 
  c AS (
    SELECT ContentId, STRING_AGG(TagId, ',') WITHIN GROUP (ORDER BY TagId) tags
    FROM ContentTag
    GROUP BY ContentId
  ),
  f AS (
    SELECT FolderId, STRING_AGG(TagId, ',') WITHIN GROUP (ORDER BY TagId) tags
    FROM FolderTag
    GROUP BY FolderId
  )  
SELECT c.ContentId, f.FolderId
FROM c INNER JOIN f
ON f.tags = c.tags


ContentId
FolderId


48
2