Skip to content
Advertisement

Joining two tables on multiple values of the joining column

I am stuck in situation where I am unable to make any progress. The requirement is below.

I have two tables one is 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

This contains the linking between Content table and tag table

and there is foldettag table which contains the linking between folder table and tag table

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         

the folder will contain only those contents which are linked to all the tags which are liked to the folder.

Note: I need the result whihc contains the link of those contentId and FolderId which are linked to exactly same tags.

for example it will contain the following result

ContentId     FolderId
   48            2

Can someone plz suggest to me some approach which I should follow to proceed with this?

I am trying to find a solution for the past few hours but unable to find any solution Thank you

Advertisement

Answer

declare @ContentTag table
(
Id int identity,
ContentId int,
TagId int
);
insert into @ContentTag(ContentId, TagId)
values
(48, 3), (48, 4), (48, 5), (48, 6),
(47, 7), (47, 8), (47, 1), (47, 2), 
(46, 4), (46, 6);

declare @FolderTag table
(
Id int identity,
FolderId int,
TagId int
);

insert into @FolderTag(FolderId, TagId)
values
(2, 3), (2, 4), (2, 5), (2, 6),
(4, 3), (5, 1), (5, 2), (5, 3), (10, 1),
(11, 4), (11, 6);

select f.FolderId, c.ContentId
from 
(
    select FolderId, TagId, count(*) over(partition by FolderId) as FolderTagCount
    from @FolderTag
) as f
join
(
    select ContentId , TagId, count(*) over(partition by ContentId) as ContentTagCount
    from @ContentTag
) as c on f.TagId = c.TagId and f.FolderTagCount = c.ContentTagCount
group by f.FolderId, c.ContentId
having count(f.TagId) = max(c.ContentTagCount);
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement