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