Pfthb

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

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