Match ALL (not ANY) values from a WHERE clause

Question

TL;DR How do I find rows in a table that match ALL (not ANY) rows from another table?

This seems so simple but I don't know the correct terminology, so am seeing dozens of answers that use INNER JOIN, INTERSECT, EXISTS or ALL, but don't achieve what I need. The other questions are either PostgreSQL, dynamically generated SQL via the application, or are unanswered.

Take the following people who like different colors:

DECLARE @tbl TABLE (
    FirstName nvarchar(50),
    Color nvarchar(50)
);
INSERT INTO @tbl 
    (FirstName, Color) 
VALUES
    ('Bob',   'Purple'),
    ('Bob',   'Red'), 
    ('Bob',   'Yellow'), 
    ('Fred',  'Purple'),
    ('Fred',  'Red'), 
    ('Fred',  'Yellow'),
    ('Greg',  'Orange'), 
    ('Greg',  'Red'), 
    ('Harry', 'Red');

I need to find people who like ALL of the colors I'm searching for.

DECLARE @SearchColors TABLE (SearchColor nvarchar(50));
INSERT INTO @SearchColors (SearchColor) VALUES ('Red'),('Yellow');

So I would only expect to see Bob and Fred in the results, because only those two people like ALL of the colours I'm searching for. I don't want people who only like a single colour, however, it doesn't matter if people like more than both of those colours (e.g. Bob likes 3 colors, including the two I need).

Reading through books online, I found ALL, which appeared close to what I need, but actually finds nothing (unless I'm using it wrong):

SELECT
   *
FROM
   @tbl
WHERE
   (Color = ALL ( SELECT SearchColor FROM @SearchColors ));

user_5464747 · Answer

What about getting the total count of colors you are looking for, filtering your main table for those, then counting names that have that many entries?

DECLARE @colorcount INT = (SELECT COUNT(DISTINCT SearchColor) FROM @SearchColors)

SELECT firstname
FROM @tbl 
WHERE color IN (SELECT searchcolor FROM @SearchColors)
GROUP BY firstname
HAVING COUNT(DISTINCT color) = @colorcount

This will handle dupes in your @tbl if ('Greg', 'Red') is in there twice or if your @searchcolors table has ('Red') twice

Match ALL (not ANY) values from a WHERE clause

1 Answers