How do I select rows in table (A) sharing the same foreign key (itemId) where multiple rows in table have the values in table B

Question

Sorry about the title, not sure how to describe without example. I trying to implement faceting of attributes in SQL Server 2008.

I have 2 tables. itemAttributes and facetParameters

Assume the following values in itemAttributes

id,   itemId,   name,      value
---------------------------------------
1     1         keywords   example1
2     1         keywords   example2
3     2         color      red
4     2         keywords   example1
5     2         keywords   example2
6     3         keywords   example2
7     3         color      red
8     3         color      blue

Assume the following values in facetParameters

name      value
----------------------
keywords  example1
color     red

I need to retrieve the (optional: distinct) itemIds where a given itemId has rows that contain all the values in facetParameters.

e.g. given the rows in facetParameters the query should return itemId 2. At the moment I would be using this in a CTE however given that they do not support a number of features I can work around this if there is no solution that works inside a CTE.

I have done a fair bit of sql over the years but this one has really stumped me and the shame is I keep thinking the answer must be simple.

Answer 1

You could join both tables, and use a having clause to ensure that all items match:

select     ia.itemid
from       @itemAttributes ia
inner join @facetParameters fp
on         ia.name = fp.name
           and ia.value = fp.value
group by   ia.itemid
having     count(distinct fp.name) = 
           (
           select count(*) from @facetParameters
           )

The count in the having clause assumes that the name uniquely identifies a row in the facetParameters table. If it doesn't, add an identity column to facetParameters, and use count(distinct id_column) instead of count(distinct fp.name).

Here's code to create the data set in the question:

declare @itemAttributes table (id int, itemId int, 
    name varchar(max), value varchar(max))
insert into @itemAttributes
select 1,1,'keywords','example1'
union all select 2,1,'keywords','example2'
union all select 3,2,'color','red'
union all select 4,2,'keywords','example1'
union all select 5,2,'keywords','example2'
union all select 6,3,'keywords','example2'
union all select 7,3,'color','red'
union all select 8,3,'color','blue'

declare @facetParameters table (name varchar(max), value varchar(max))
insert into @facetParameters
select 'keywords','example1'
union all select 'color','red'