SQL Query to Filter a Table using another Table

Question

I currently have 2 SQL tables that look like this:

Data Table

and...

filter table

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:

Result table

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

score 17 · Accepted Answer · answered Sep 18 '13 at 00:44

17

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

answered Sep 18 '13 at 00:44

sgeddes

62,311
6
61
83

This is a good solution, however I wonder if there is a way to do it without the use of a counter? – StevenP Sep 18 '13 at 00:50
I'm curious. How is it possible that "HAVING COUNT(*) = (SELECT COUNT(*) FROM FilterTable)" specify and select value 1 and 4? Count is used for counting amout of row in the table. – HelloWorld1 May 20 '15 at 08:10

Hotchips · Answer 2 · 2013-09-18T01:11:29.857

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.

edited Sep 18 '13 at 01:11

answered Sep 18 '13 at 00:53

Hotchips

621
5
18

Shouldn't this be Data.Value = Filter.Value ? (as you are using a NOT IN) – AlexT82 Sep 18 '13 at 00:59
1

This will, however, return IDs where the ID doesn't have a field matching the filter. (So if you have a new field, say "Active2", and filter on that, all of the IDs shown above will be returned. That's because we're specifically looking for Instances where the filter doesn't match.) – Hotchips Sep 18 '13 at 01:00
2

Nice try, but this won't always work -- take this for example: http://sqlfiddle.com/#!3/71703/1 -- with that said, I wonder if you're not on to something simpler... – sgeddes Sep 18 '13 at 01:00
@user2697139 Nope. This looks for all the IDs where the filters *don't* match, and then filters those out. Certainly, it returns the results the OP was looking for. Unfortunately, it also returns any where none of the filters *exist* against that ID. Not sure if that's what the OP wanted. – Hotchips Sep 18 '13 at 01:02
@Sgeddes This version works better, but still isn't perfect. It fits your example, but not this one: http://sqlfiddle.com/#!3/fddd5/1 – Hotchips Sep 18 '13 at 01:10
Not sure you're 2nd query helps -- http://sqlfiddle.com/#!3/1ea41/1 -- that should only return ID 4... I think you have to check the count as posted by @sgeddes. – sgeddes Sep 18 '13 at 01:10
2

@Hotchips -- yeah, I've been playing with it for a few minutes. Not sure how to get around using `COUNT`. Either way, +1 for effort :D – sgeddes Sep 18 '13 at 01:11

score 0 · Answer 3 · answered Sep 18 '13 at 07:15

In case you can use sp_executesql (you are using procedure).

SET NOCOUNT ON
GO

    CREATE TABLE Data  
    (
         [ID] INT
        ,[Name] VARCHAR(12)
        ,[Value] VARCHAR(2)
    )

    CREATE TABLE Filter  
    (
         [Name] VARCHAR(12)
        ,[Value] VARCHAR(2)
    )

    INSERT INTO Data ([ID], [Name], [Value])
    VALUES   (1, 'productname', 'A')
            ,(1, 'cost', '20')
            ,(1, 'active', 'Y')
            ,(2, 'productname', 'A')
            ,(2, 'cost', '20')
            ,(2, 'active', 'N')
            ,(3, 'productname', 'B')
            ,(3, 'cost', '20')
            ,(3, 'active', 'Y')
            ,(4, 'productname', 'A') 
            ,(4, 'cost', '20')
            ,(4, 'active', 'Y')

    INSERT INTO Filter ([Name], [Value])
    VALUES ('productname', 'A')
          ,('active', 'Y')

    DECLARE @SQLColumns NVARCHAR(MAX) = SUBSTRING((SELECT DISTINCT ',[' +[Name]  +']' FROM Data FOR XML PATH('')),2,4000)
    DECLARE @SQLFilterColumns NVARCHAR(MAX) = SUBSTRING((SELECT 'AND [' +[Name]  +'] = ''' + [Value] + ''' ' FROM Filter FOR XML PATH('')),4,4000)

    DECLARE @SQLStatement NVARCHAR(MAX) = N'
    ;WITH DataSource ([ID]) AS
    (
        SELECT [ID]
        FROM
        (
            SELECT [ID]
                  ,[Name]
                  ,[Value]
            FROM Data
        ) DataSource
        PIVOT
        (
            MAX([Value]) FOR [Name] IN (' + @SQLColumns+  ')
        ) PVT
        WHERE ' +  @SQLFilterColumns + '
    )
    SELECT DT.[ID]
          ,DT.[Name]
          ,DT.[Value]
    FROM Data DT
    INNER JOIN DataSource DS
        ON DT.[ID] = DS.[ID]
    '

    EXECUTE sp_executesql @SQLStatement

    DROP TABLE Data
    DROP TABLE Filter

SET NOCOUNT OFF
GO

score 0 · Answer 4 · answered Dec 04 '15 at 14:07

Here is an option using a couple of PIVOTs

DECLARE @Data table ([ID] INT, [Name] VARCHAR(12), [Value] VARCHAR(2) )

DECLARE @Filter TABLE ( [Name] VARCHAR(12), [Value] VARCHAR(2)    )

    INSERT INTO @Data ([ID], [Name], [Value])
    VALUES   (1, 'productname', 'A')
            ,(1, 'cost', '20')
            ,(1, 'active', 'Y')
            ,(2, 'productname', 'A')
            ,(2, 'cost', '20')
            ,(2, 'active', 'N')
            ,(3, 'productname', 'B')
            ,(3, 'cost', '20')
            ,(3, 'active', 'Y')
            ,(4, 'productname', 'A') 
            ,(4, 'cost', '20')
            ,(4, 'active', 'Y')

    INSERT INTO @Filter ([Name], [Value])
    VALUES ('productname', 'A')
          ,('active', 'Y');

SELECT * 
FROM (  SELECT *
        FROM (select [ID], [Name], [value] from @Data) as s
        PIVOT 
        ( MAX([value]) FOR [name] in ( [productname], [active])
        ) as pvt) B
INNER JOIN 
        (   SELECT * 
        FROM (select [name], [value] from @Filter) as f
        PIVOT
        ( MAX([value]) for [Name] IN ([productname], [active]) 
        ) AS fpvt
    ) F 
ON F.active = b.active and f.productname = b.productname

By doing a PIVOT on the DATA table and then on the FILTER table, it allows them to be lined up for an inner join. This returns the records that match within both,

SQL Query to Filter a Table using another Table

4 Answers4