How can I structure a query to give me only the rows that match ALL values in a CSV list of IDs in T-SQL

sql, sql-server, sql-server-2008, t-sql

Solution

Use the below splitter function which returns an int column. So it's easy to check the count in the HAVING clause.

CREATE FUNCTION [dbo].[DelimitedParamParser]( @DelimitedIds VARCHAR(MAX), @Delimiter CHAR(1)) 
RETURNS @IdsTable 
TABLE ( Id INT ) 
AS BEGIN

DECLARE @Length INT,
        @Index INT,
        @NextIndex INT

SET @Length = DATALENGTH(@DelimitedIds)
SET @Index = 0
SET @NextIndex = 0


WHILE (@Length > @Index )
BEGIN
    SET @NextIndex = CHARINDEX(@Delimiter, @DelimitedIds, @Index)
    IF (@NextIndex = 0 ) SET @NextIndex = @Length + 2
        INSERT @IdsTable SELECT SUBSTRING( @DelimitedIds, @Index, @NextIndex - @Index )
    SET @index = @nextindex + 1
END
 RETURN
END

This works, keep in mind to give an extra comma at the end.

DECLARE @DELIMITER CHAR = ','
DECLARE @CSV_STRING VARCHAR(20) = '1,3,'

SELECT Distinct SUPER_HERO.NAME, SKILL.NAME
FROM 
    SUPER_HERO
    INNER JOIN SUPER_HERO_SKILL ON  SUPER_HERO_SKILL.SUPER_HERO_ID = SUPER_HERO.ID
    INNER JOIN SKILL ON SUPER_HERO_SKILL.SKILL_ID = SKILL.ID
    WHERE SUPER_HERO.ID IN
    (
    SELECT SUPER_HERO_SKILL.SUPER_HERO_ID   
    FROM 
        SUPER_HERO
        INNER JOIN SUPER_HERO_SKILL ON  SUPER_HERO_SKILL.SUPER_HERO_ID = SUPER_HERO.ID
        INNER JOIN SKILL ON SUPER_HERO_SKILL.SKILL_ID = SKILL.ID
        INNER JOIN DelimitedParamParser(@CSV_STRING, @DELIMITER) SPLIT  ON SPLIT.ID = SUPER_HERO_SKILL.SKILL_ID
    GROUP BY SUPER_HERO_SKILL.SUPER_HERO_ID
    HAVING COUNT(DISTINCT(SUPER_HERO_SKILL.SKILL_ID)) = (SELECT COUNT(DISTINCT(Id)) FROM DelimitedParamParser(@CSV_STRING, @DELIMITER))
    )

Problem

I've created a simple example (hopefully much more fun than my actual data) to better express my question: ``` CREATE TABLE SUPER_HERO ( ID INT, NAME VARCHAR(50) ) INSERT INTO SUPER_HERO VALUES (1, 'Storm') INSERT INTO SUPER_HERO VALUES (2, 'Silver Surfer') INSERT INTO SUPER_HERO VALUES (3, 'Spider Man') CREATE TABLE SKILL ( ID INT, NAME VARCHAR(50) ) INSERT INTO SKILL VALUES (1, 'Flight') INSERT INTO SKILL VALUES (2, 'Weather Control') INSERT INTO SKILL VALUES (3, 'Super Speed') CREATE TABLE SUPER_HERO_SKILL ( SUPER_HERO_ID INT, SKILL_ID INT ) INSERT INTO SUPER_HERO_SKILL VALUES (1, 1) --Storm has Flight INSERT INTO SUPER_HERO_SKILL VALUES (1, 2) --Storm has Weather Control INSERT INTO SUPER_HERO_SKILL VALUES (2, 1) --Silver Surfer has Flight INSERT INTO SUPER_HERO_SKILL VALUES (2, 3) --Silver Surfer has Super Speed INSERT INTO SUPER_HERO_SKILL VALUES (3, 3) --Spider Man has Super Speed ``` Example of bad query (not showing desired results): ``` DECLARE @DELIMITER CHAR = ',' DECLARE @CSV_STRING VARCHAR(20) = '1,3' SELECT SUPER_HERO_NAME = SUPER_HERO.NAME, SKILL_NAME = SKILL.NAME FROM SUPER_HERO JOIN SUPER_HERO_SKILL ON SUPER_HERO_SKILL.SUPER_HERO_ID = SUPER_HERO.ID JOIN SKILL ON SUPER_HERO_SKILL.SKILL_ID = SKILL.ID JOIN dbo.Split(@CSV_STRING, @DELIMITER) SPLIT ON SPLIT.ITEMS = SKILL.ID ``` What I would like to see: When `DECLARE @CSV_STRING VARCHAR(20) = '1,3'` I should only see "Silver Surfer" since he is the only one with both skills 1 and 3 which correlate to Flight and Super Speed. When `DECLARE @CSV_STRING VARCHAR(20) = '1,2,3'` I should not see any heroes in my universe since there are none defined to have all three skills listed. There must be something simple that I am missing. I have tried structuring the query many different ways. I have presented the simplest form of it here as not to complicate the presentation of problem. Note: I use a function that acts as a Split based on delimiter passed in.

Original source