filter a table based on a second table matching all criteria

Question

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,

You need to show some effort on your part. What is the query you are using now? Included it. — James Jenkins, Sep 06 '19 at 17:13

score 2 · Accepted Answer · answered Sep 06 '19 at 16:23

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

Very nice +1! I'd would have upvoted twice if I could! I created a fiddle here which shows the thought processes behind the SQL (postgresql version here - the only change required was removal of # from table names. Works for MySQL. SQLite also! 4 upvotes! :-) — Vérace, Sep 06 '19 at 17:09

filter a table based on a second table matching all criteria

1 Answers1

Linked