SQL Query to Filter a Table using another Table

Question

I currently have 2 SQL tables that look like this:

and...

I need to write a SELECT statement that retrieves all products from the DataTable that contain rows that match the FilterTable.

So based on my example tables above, if I were to run the query, it would return the following result:

I recently found a question that kind of attempts this: SQL query where ALL records in a join match a condition? but have been unsuccessful in implementing something similar

Note - I am using Microsoft SQL Server 2008

Answer 1

This is a little complicated, but here is one solution. Basically you need to check to see how many records from the datatable match all the records from the filtertable. This uses a subquery to do that:

SELECT *
FROM DataTable
WHERE ID IN (
  SELECT DT.ID
  FROM DataTable DT
    JOIN FilterTable FT ON FT.Name = DT.Name 
          AND FT.Value = DT.VALUE
  GROUP BY DT.ID
  HAVING COUNT(*) = (SELECT COUNT(*) FROM FilterTable)
)  

• SQL Fiddle Demo

Answer 2

This will work:

SELECT * FROM Data WHERE ID NOT IN (
    SELECT ID FROM Data JOIN Filter 
       on Data.Name = Filter.Name and Data.Value <> Filter.Value
)

I set up a SQL Fiddle if you want to try other things: http://sqlfiddle.com/#!3/38b87/6

EDIT:

Better answer:

SELECT *
FROM DATA
WHERE ID NOT IN (
  SELECT ID
  FROM DATA
  JOIN Filter ON DATA.Name = Filter.Name
    AND DATA.Value <> Filter.Value
) AND ID IN
(
  SELECT ID 
  FROM DATA 
  JOIN Filter ON DATA.Name = Filter.Name
)

This now fits where there is at least one filter that matches, and none that don't.