In operator matching all rows

Question

I want to return matching all values of csv as the traditional "in" operator matches any of the items present in csv:

SELECT * FROM @MyTable
WHERE [UserID] IN (1,2)

The above query will not serve my purpose as I want to match the rows which have both records for a group. In my case group will by typeid.

Query to populate the table:

DECLARE @MyTable TABLE
    (
      [TypeID] INT ,
      [UserID] INT
    )

INSERT  INTO @MyTable
        SELECT  1 ,
                1
        UNION
        SELECT  1 ,
                2
        UNION
        SELECT  2 ,
                1
        UNION
        SELECT  2 ,
                2
        UNION
        SELECT  2 ,
                3
        UNION
        SELECT  3 ,
                1
        UNION
        SELECT  3 ,
                2
        UNION
        SELECT  3 ,
                3
        UNION
        SELECT  3 ,
                4 

To query the above table I have input string of userid

DECLARE @UserIDString VARCHAR(256)

Here is my requirement:

When the input is '1,2'; I want typeid 1 as the output as that group has all the records present in csv.

If the input is '1,2,3' ; 2 typeid should be returned as that group has all the values present in csv.

If the input is '1,2,3,4' ; 3 typeid should be returned as that group has all the values present in csv.

EDIT:

Here is the split function to split the csv:

CREATE FUNCTION [dbo].[Split_String]
    (
      @inputString NVARCHAR(2000) ,
      @delimiter NVARCHAR(20) = ' '
    )
RETURNS @Strings TABLE
    (
      [position] INT IDENTITY
                     PRIMARY KEY ,
      [value] NVARCHAR(2000)
    )
AS 
    BEGIN  
        DECLARE @index INT  

        SET @index = -1  

        WHILE ( LEN(@inputString) > 0 ) 
            BEGIN-- Find the first delimiter  

                SET @index = CHARINDEX(@delimiter, @inputString)  
-- No delimiter left?  

-- Insert the remaining @inputString and break the loop  

                IF ( @index = 0 )
                    AND ( LEN(@inputString) > 0 ) 
                    BEGIN  

                        INSERT  INTO @Strings
                        VALUES  ( RTRIM(LTRIM(CAST(@inputString AS NVARCHAR(2000))) ))  

                        BREAK  

                    END  

-- Found a delimiter  

-- Insert left of the delimiter and truncate the @inputString  

                IF ( @index > 1 ) 
                    BEGIN  

                        INSERT  INTO @Strings
                        VALUES  ( RTRIM(LTRIM(CAST(LEFT(@inputString, @index - 1) AS NVARCHAR(2000)) )  ))

                        SET @inputString = RIGHT(@inputString,
                                                 ( LEN(@inputString) - @index ))  
                    END -- Delimiter is 1st position = no @inputString to insert  

                ELSE 
                    SET @inputString = CAST(RIGHT(@inputString,
                                                  ( LEN(@inputString) - @index )) AS NVARCHAR(2000))  
            END
        RETURN  

    END
GO

Edit:

Thanks @Tab, with further modifications I have come to solution:

DECLARE @InputString VARCHAR(256)
DECLARE @Count VARCHAR(256)

--SET @InputString = '1,2'

DECLARE @DummyTable TABLE
    (
      [position] INT ,
      [value] INT
    )
INSERT  INTO @DummyTable
        ( [position] ,
          [value]
        )
        SELECT  [position] ,
                [value]
        FROM    [dbo].[Split_String](@InputString, ',')

SELECT  @Count = COUNT(1)
FROM    @DummyTable

SELECT  TypeID
FROM    @MyTable
WHERE   TypeID NOT IN (
        SELECT  TypeID
        FROM    @MyTable T
                LEFT OUTER JOIN @DummyTable ss ON t.UserId = ss.Value
        WHERE   ss.Position IS NULL )
GROUP BY TypeID
HAVING  COUNT(TypeID) = @Count

Answer 1

Using your split function, you can do an OUTER JOIN and make sure there are no NULL rows:

SELECT TypeID 
FROM @MyTable
WHERE TypeID NOT IN (
  SELECT TypeID
  FROM @MyTable t
  LEFT OUTER JOIN [dbo].[Split_String] (@InputString,',') ss
    ON t.UserId=ss.Value
  WHERE ss.Position IS NULL
) x

Untested, but I think that should do it.

However, this should return ALL the types that meet the requirement of:

that group has all the records present in csv.

In your question, you seem to imply that only one row should be returned, but why would that be the case if more than one row matches all the values in the csv? And what is the rule for determining which row is returned when there is more than one match?