sql server - Help with a sql search query using a comma delimitted parameter

Question

I am looking for something like this but can't figure out the best way to write the query:

SELECT DISTINCT CategoryID FROM tbl_Categories c INNER JOIN 
  mappingTable mp ON c.CategoryID = mp.CategoryID INNER JOIN
  SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
WHERE sc.SubcategoryID IN ALL (234,245,645)

I am currently building a dynamic query since the IDs are passed in as a comma delimitted string '234,245,645' But as we all know there is no such thing as ALL. Basically I want to return all Categories that are have all of the sub categories in the list. Hope this makes sense.

Answer 1

you can do this:

SOLUTION 1

SELECT CategoryID
FROM tbl_Categories c INNER JOIN 
  mappingTable mp ON c.CategoryID = mp.CategoryID INNER JOIN
  SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
WHERE sc.SubcategoryID IN (234,245,645)
GROUP BY CategoryID
HAVING COUNT(sc.SubcategoryID)
       = LEN(
          REPLACE(
           REPLACE(
            REPLACE(
             REPLACE(
              REPLACE(
               REPLACE(
                REPLACE(
                 REPLACE(
                  REPLACE(
                   REPLACE(
                    REPLACE('234,245,645','0','')
                   , '1', '')
                  , '2', '')
                 , '3', '')
                , '4', '')
               , '5', '')
              , '6', '')
             , '7', '')
            , '8', '')
           , '9', '')
          , ' ', '')) + 1

SOLUTION 2: another that may work:

SELECT CategoryID
FROM tbl_Categories c INNER JOIN 
  mappingTable mp ON c.CategoryID = mp.CategoryID INNER JOIN
  SubCategories sc ON mp.SubCategoryID = sc.SubCategoryID
WHERE sc.SubcategoryID IN (234,245,645)
GROUP BY CategoryID
HAVING COUNT(sc.SubcategoryID) 
       = (SELECT COUNT(DISTINCT SubcategoryID)
            FROM SubCategories 
           WHERE SubcategoryID IN (234,245,645))