create table Product (product_key int identity(1,1) PRIMARY KEY, productName nvarchar(100))
go

create table Feature (feature_key int identity(1,1) PRIMARY KEY, FeatureName nvarchar(100))
go

create table ProdFeatureLink (ProdFeatureLink_key int identity(1,1) PRIMARY KEY, Product_Fkey int, Feature_fkey int)
go

insert into Feature (featureName) values ('Feature1')
insert into Feature (featureName) values ('Feature2')
insert into Feature (featureName) values ('Feature3')


declare @prodKey int
insert into product (ProductName) values('Product1')
set @prodKey = SCOPE_IDENTITY()

insert into prodFeatureLink(product_fkey, feature_fkey) 
select @prodkey, feature_key from feature where featureName in ('Feature1')

insert into product (ProductName) values('Product2')
set @prodKey = SCOPE_IDENTITY()

insert into prodFeatureLink(product_fkey, feature_fkey) 
select @prodkey, feature_key from feature where featureName in ('Feature1', 'Feature2')

insert into product (ProductName) values('Product3')
set @prodKey = SCOPE_IDENTITY()

insert into prodFeatureLink(product_fkey, feature_fkey) 
select @prodkey, feature_key from feature where featureName in ('Feature1', 'Feature2', 'Feature3')


---summary of the data set up

select 
	p.product_key, P.ProductName, F.FeatureName 
from product p 
	inner join prodfeaturelink pfl on p.product_key = pfl.product_fkey
	inner join Feature f on f.feature_key = pfl.feature_fkey

--find products which  have feature 1


select P.* from
product p
	inner join ProdFeatureLink pfl on p.product_key = pfl.product_fkey
	inner join feature f on f.feature_key = pfl.feature_fkey
where 
	f.featureNAme = 'Feature1'

--Products which have all/either feature 2 or 3

select distinct p.*
from product p
	inner join prodfeaturelink pfl
		on pfl.product_fkey = p.product_key
	inner join feature f
		on f.feature_key = pfl.feature_fkey
where
	f.featureName in ('Feature2', 'Feature3')

	--products which have both Feature 2 and 3

	select p.product_key, p.productName
	from product p
		inner join ProdFeatureLink pfl on pfl.product_fkey = p.product_key
		inner join feature f on f.feature_key = pfl.feature_fkey
	where featureName in ('Feature2', 'Feature3')
	group by p.Product_key, p.ProductName
	Having Count(1) = 2

/*
--clean up after ourselves

drop table product
drop table feature
drop table prodfeaturelink
*/