You can do it with a single pass thru the item_categories table -- I renamed the tables to reflect their contents. Data set up first, then the main query at the end. Edit: Split main code from data set up code.

declare @category_name1 varchar(10)

declare @category_name2 varchar(10)

set @category_name1 = 'WHITE'

set @category_name2 = 'CIRCLE'

--------------------------------------------------------------------------------

select i.ITEM_NAME

from (

select ic.ITEM_ID

from @item_categories ic

inner join @categories c on c.CAT_ID = ic.CAT_ID

cross apply (

select case when c.CAT_NAME = @category_name1 then 1

when c.CAT_NAME = @category_name2 then 2

--when c.CAT_NAME = @category_name3 then 4 ...

else -1 end as CATEGORY_MATCH

) as ca1

group by ic.ITEM_ID

having min(CATEGORY_MATCH) > 0 and sum(CATEGORY_MATCH) = 3

) as derived

inner join @items i on i.ITEM_ID = derived.ITEM_ID

--data set up with meaningful table names

set nocount on;

DECLARE @items TABLE (ITEM_ID INT, ITEM_NAME VARCHAR(10))

INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 10,'ITEM 1'

INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 11,'ITEM 2'

INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 12,'ITEM 3'

INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 13,'ITEM 4'

INSERT INTO @items (ITEM_ID, ITEM_NAME) SELECT 14,'ITEM 5'

--SELECT * FROM @items

--------

DECLARE @categories TABLE (CAT_ID INT, CAT_NAME VARCHAR(10))

INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 100,'WHITE'

INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 101,'BLACK'

INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 102,'BLUE'

INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 103,'GREEN'

INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 104,'YELLOW'

INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 105,'CIRCLE'

INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 106,'SQUARE'

INSERT INTO @categories (CAT_ID, CAT_NAME) SELECT 107,'TRIANGLE'

--SELECT * FROM @categories

--------

DECLARE @master_categories TABLE (MASTERCAT_ID INT, CAT_ID INT)

INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,100

INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,101

INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,102

INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,103

INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 1,104

INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,105

INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,106

INSERT INTO @master_categories (MASTERCAT_ID, CAT_ID) SELECT 2,107

--SELECT * FROM @master_categories

--------

DECLARE @item_categories TABLE (CAT_ID INT, ITEM_ID INT)

INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,10

INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,10

INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,11

INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,11

INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 106,11

INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,12

INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 101,12

INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,12

INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 100,13

INSERT INTO @item_categories (CAT_ID, ITEM_ID) SELECT 105,14

--SELECT * FROM @item_categories

set nocount off