CODEWITHSUNDEEP
sqlsql-server-2008t-sqlselectfilter
StevenP
StevenP

Reputation: 177

SQL Query to Filter a Table using another Table

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

Upvotes: 4

Views: 40009

Answers (4)

Steven
Steven

Reputation: 911

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,

Upvotes: 0

gotqn
gotqn

Reputation: 43636

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

Upvotes: 0

Hotchips
Hotchips

Reputation: 633

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.

Upvotes: 2

sgeddes
sgeddes

Reputation: 62841

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)
)

Upvotes: 17

Related Questions