Select records where sub records are from a list

Question

Problem

I am trying to write a stored procedure to select a list of NewsItem records where each NewsItem has all of the Categories that are in a list. If the list is empty then it should return all news items.

NewsItem     NewsItemCategories     Category
--------     ------------------     --------
NewsID       NewsID                 CategoryID
Post         CategoryID             CategoryName

I pass a list of comma separated category names to my stored procedure and have created a function that returns a table of these categories.

exec sp_GetNewsItems 'sport,football,hockey'

EntityNameColumn - table returned from my function BuildStringTable
----------------
sport
finance
history

What I Have Tried

select NI.NewsID, NI.Post
from NewsItem NI
where (@pCategories = '' or
    (select COUNT(*)
    from NewsItemCategories NIC
    inner join Category C on NIC.CategoryID = C.CategoryID
    inner join BuildStringTable(@pCategories) CT on C.CategoryName = CT.EntityNameColumn
where NIC.NewsID = NI.NewsID) > 0)

Question

The query works if you pass it a single category name, however does not work when you pass multiple category names. In the example query above this should return NewsItems that contain, at least, Categories sport, football, hockey.

Answer 1

If you are using sql 2005 or newer, you can use the EXCEPT operator to find the news items that contain all the required categories. Or more precisely, find the news items where are no names in the list of categories that were not found in the set of news item category names.

select NI.NewsID, NI.Post
from NewsItem NI
where (@pCategories = '' or
    NOT EXISTS (select EntityNameColumn FROM BuildStringTable(@pCategories) 
        EXCEPT 
              Select CategoryName FROM NewsItem ni 
              join NewsItemCategories nic ON ni.NewsID=nic.NewsID
              join Category c ON c.CategoryID = nic.CategoryID 
        WHERE ni.NewsID=NI.NewsID))

To do this without the EXCEPT operator looks like this:

where (@pCategories = '' or
    NOT EXISTS (select EntityNameColumn FROM BuildStringTable(@pCategories) ct 
        LEFT OUTER JOIN 
              (Select CategoryName FROM NewsItem ni 
              join NewsItemCategories nic ON ni.NewsID=nic.NewsID
              join Category c ON c.CategoryID = nic.CategoryID
              WHERE ni.NewsID=NI.NewsID) nicn
        WHERE nicn.CategoryName IS NULL)