CODEWITHSUNDEEP
sql-serverjoin
Icerman
Icerman

Reputation: 1119

Find many to many supersets in SQL Server

I have three tables: File, Word and WordInstance:

CREATE TABLE [dbo].[File](
    [FileId] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [nchar](10) NOT NULL)

CREATE TABLE [dbo].[Word](
    [WordId] [int] IDENTITY(1,1) NOT NULL,
    [Word] [nchar](10) NOT NULL,
    [LatestFileId] [int] NOT NULL)

CREATE TABLE [dbo].[WordInstance](
    [WordInstanceId] [int] IDENTITY(1,1) NOT NULL,
    [WordId] [int] NOT NULL,
    [FileId] [int] NOT NULL)

Note that I have omitted the foreign keys to make this concise. Given a FileId I want to return a true/false value which tells me whether there are other files that have the same words as the specified FileId.

Started with this, I know it is not working but provided as is:

CREATE FUNCTION [dbo].[DoesFileContainLastWord]
(
    @fileId INT
)
RETURNS BIT
AS
BEGIN
    DECLARE @count  INT
    DECLARE @ret    BIT

    SELECT @count = COUNT([tW].[WordId])
    FROM [Word] AS tW
    INNER JOIN [WordInstance] AS tWI 
        ON [tW].[WordId] = [tWI].[WordId]
    INNER JOIN [File] AS tF
        ON [tF].[FileId] = [tW].[LatestFileId]
    WHERE [tF].[FileId] = @fileId

    IF @count > 0
        BEGIN
            SET @ret = 0
        END
    ELSE
        SET @ret = 1

    RETURN @ret

END;

Upvotes: 0

Views: 163

Answers (1)

Esoteric Screen Name
Esoteric Screen Name

Reputation: 6112

Tested on SQL Server 2005:

declare @file table (fileid int)
declare @instance table (fileid int,wordid int)
insert into @file (fileid)
select 1 union all select 2
insert into @instance (fileid,wordid) 
select 1,1 
union all select 1,2 
union all select 1,3 
union all select 2,1 
union all select 2,2
declare @fileid int
set @fileid=2
;with fvalues as
(
    select distinct wordid from @instance where fileid=@fileid
)
select case when exists
(
    select * 
    from fvalues v
    inner join @instance i on v.wordid = i.wordid
    and i.fileid<>@fileid
    group by i.fileid
    having count(distinct i.wordid) >= (select count(*) from fvalues)
) 
then cast(1 as bit)
else cast(0 as bit) end

Returns 0 for @fileid=1 and 1 for @fileid=2, as file 1's word set is a proper superset of file 2's.

Upvotes: 1

Related Questions