SQL Server – Filter a Table Based on a Second Table Matching All Criteria

sql servert-sql

Having trouble writing a query to return rows from one table matching rows in a second table. Simple joins I can pull in combinations, but how do I get only those rows that match ALL of the criteria?

For example:

Attribute Table

+---------+------------+------------+
|  itemno | attrname   | attrvalue  |
+---------+------------+------------+
+    1    +  Color     +  Black     +
+    1    +  Connector +  3.5mm     +
+    1    +  Output    +  5W        +
+    2    +  Color     +  Black     +
+    2    +  Connector +  USB       +
+    2    +  Output    +  10W       +
+    3    +  Color     +  Silver    +
+    3    +  Connector +  3.5mm     +
+    3    +  Output    +  5W        +
+    4    +  Color     +  White     +
+    4    +  Connector +  USB       +
+    4    +  Output    +  2W        +
+---------+------------+------------+

Filter Table 1

+------------+------------+
| attrname   | attrvalue  |
+------------+------------+
+  Color     +  Black     +
+  Color     +  Silver    +
+  Output    +  5W        +
+------------+------------+

would return only items 1 and 3, but NOT item 2 because it didn't match both criteria in the filter.

thanks in advance,

Best Answer

This may not be the best performing way to do this but it should work for you.

Set up code: (Anyone else wanting to answer feel free to copy this part)

CREATE TABLE #Attrib (itemno int, attrname varchar(50), attrvalue varchar(50));
INSERT INTO #Attrib VALUES 
    (1, 'Color', 'Black'),
    (1, 'Connector', '3.5mm'),
    (1, 'Output', '5W'),

    (2, 'Color', 'Black'),
    (2, 'Connector', 'USB'),
    (2, 'Output', '10W'),

    (3, 'Color', 'Silver'),
    (3, 'Connector', '3.5mm'),
    (3, 'Output', '5W'),

    (4, 'Color', 'White'),
    (4, 'Connector', 'USB'),
    (4, 'Output', '2W');


CREATE TABLE #Filter1 (attrname varchar(50), attrvalue varchar(50));

INSERT INTO #Filter1 VALUES 
    ('Color','Black'),
    ('Color','Silver'),
    ('Output','5W');
GO

Query:

SELECT DISTINCT itemno
FROM #Attrib A
JOIN #Filter1 F
    ON A.attrname = F.attrname
    AND A.attrvalue = F.attrvalue
GROUP BY itemno
HAVING COUNT(DISTINCT A.attrname) = (SELECT COUNT(DISTINCT attrname) FROM #Filter1);
Related Question