Find data by multiple Lookup table clauses

Question

declare @Character table (id int, [name] varchar(12));

insert into @Character (id, [name])
values
(1, 'tom'),
(2, 'jerry'),
(3, 'dog');

declare @NameToCharacter table (id int, nameId int, characterId int);

insert into @NameToCharacter (id, nameId, characterId)
values
(1, 1, 1),
(2, 1, 3),
(3, 1, 2),
(4, 2, 1);

The Name Table has more than just 1,2,3 and the list to parse on is dynamic

NameTable

id  | name
----------
1      foo
2      bar
3      steak

CharacterTable

id | name
---------
1     tom
2     jerry
3     dog

NameToCharacterTable

id | nameId | characterId
1     1           1
2     1           3
3     1           2
4     2           1 

I am looking for a query that will return a character that has two names. For example With the above data only "tom" will be returned.

SELECT * 
FROM nameToCharacterTable
WHERE nameId in (1,2)

The in clause will return every row that has a 1 or a 3. I want to only return the rows that have both a 1 and a 3.

I am stumped I have tried everything I know and do not want to resort to dynamic SQL. Any help would be great

The 1,3 in this example will be a dynamic list of integers. for example it could be 1,3,4,5,.....

Answer 1

Filter out a count of how many times the Character appears in the CharacterToName table matching the list you are providing (which I have assumed you can convert into a table variable or temp table) e.g.

declare @Character table (id int, [name] varchar(12));

insert into @Character (id, [name])
values
(1, 'tom'),
(2, 'jerry'),
(3, 'dog');

declare @NameToCharacter table (id int, nameId int, characterId int);

insert into @NameToCharacter (id, nameId, characterId)
values
(1, 1, 1),
(2, 1, 3),
(3, 1, 2),
(4, 2, 1);

declare @RequiredNames table (nameId int);

insert into @RequiredNames (nameId)
values
(1),
(2);

select *
from @Character C
where (
    select count(*)
    from @NameToCharacter NC
    where NC.characterId = c.id
    and NC.nameId in (select nameId from @RequiredNames)
) = 2;

Returns:

id	name
1	tom

Note: Providing DDL+DML as shown here makes it much easier for people to assist you.

Answer 2

This is classic Relational Division With Remainder.

There are a number of different solutions. @DaleK has given you an excellent one: inner-join everything, then check that each set has the right amount. This is normally the fastest solution.

If you want to ensure it works with a dynamic amount of rows, just change the last line to

) = (SELECT COUNT(*) FROM @RequiredNames);

Two other common solutions exist.

• Left-join and check that all rows were joined

SELECT *
FROM @Character c
WHERE EXISTS (SELECT 1
    FROM @RequiredNames rn
    LEFT JOIN @NameToCharacter nc ON nc.nameId = rn.nameId AND nc.characterId = c.id
    HAVING COUNT(*) = COUNT(nc.nameId)  -- all rows are joined
);

• Double anti-join, in other words: there are no "required" that are "not in the set"

SELECT *
FROM @Character c
WHERE NOT EXISTS (SELECT 1
    FROM @RequiredNames rn
    WHERE NOT EXISTS (SELECT 1
        FROM @NameToCharacter nc
        WHERE nc.nameId = rn.nameId AND nc.characterId = c.id
    )
);

A variation on the one from the other answer uses a windowed aggregate instead of a subquery. I don't think this is performant, but it may have uses in certain cases.

SELECT *
FROM @Character c
WHERE EXISTS (SELECT 1
    FROM (
      SELECT *, COUNT(*) OVER () AS cnt
      FROM @RequiredNames
    ) rn
    JOIN @NameToCharacter nc ON nc.nameId = rn.nameId AND nc.characterId = c.id
    HAVING COUNT(*) = MIN(rn.cnt)
);

db<>fiddle