Find data by multiple Lookup table clauses

September 15, 2022

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,…..

>Solution :

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: