title: Complicated SQL Query--finding items matching multiple different foreign keys
body: <p>So imagine that you have a table of <code>Products (ID int, Name nvarchar(200))</code>, and two other tables, <code>ProductsCategories (ProductID int, CategoryID int)</code> and <code>InvoiceProducts (InvoiceID int, ProductID int)</code>.  </p>
  
  <p>I need to write a query to produce a set of products that match a given set of invoice ids and category ids such that the list of products match all the specified categories and all the specified invoices, without falling back to dynamic SQL.  Imagine I need to find a list of products that are in both categories 1 and 2 and in invoices 3 and 4.</p>
  
  <p>As a start, I've written a stored-procedure that accept the category ids and invoice ids as strings, and parse them into tables:</p>
  
  <pre><code> CREATE PROCEDURE dbo.SearchProducts (@categories varchar(max), @invoices varchar(max))
   AS BEGIN
        with catids as (select cast([value] as int) from dbo.split(@categories, ' ')),
             invoiceids as (select cast([value] as int) from dbo.split(@invoices, ' '))
             select * from products --- insert awesomeness here
   END
  </code></pre>
  
  <p>The different solutions I've come up with look awful, and perform worse.  The best thing I've found is to generate a view comprised of left joins of all the criteria, but that seems very expensive and doesn't solve the issue of matching all of the different keys specified.</p>
  
  <hr>
  
  <p><strong>Update:</strong>  This is an example query I wrote that yields the expected results.  Am I missing any optimization opportunities?  Like magical unicorn matrix operations by ninjas?</p>
  
  <pre><code>with catids as (select distinct cast([value] as int) [value] from dbo.split(@categories, ' ')),
    invoiceids as (select distinct cast([value] as int) [value] from dbo.split(@invoices, ' '))
  
    select pc.ProductID from ProductsCategories pc (nolock)
      inner join catids c on c.value = pc.CategoryID 
      group by pc.ProductID 
      having COUNT(*) = (select COUNT(*) from catids)  
    intersect
    select ip.ProductID from InvoiceProducts ip (nolock)
      inner join invoiceids i on i.value = ip.InvoiceID 
      group by ip.ProductID 
      having COUNT(*) = (select COUNT(*) from invoiceids)   
  </code></pre>