Help:Complex sql query

Hi

"Product" Table
ProductID ProductName
1 Cellphone1
2 Cellphone2
3 Cellphone3

"Features" Table
FeatureID FeatureName
1 GPRS
2 Radio
3 Camera

"ProductFeatures" Table
ProductID FeatureID FeatureDescription
1 1 available
1 3 5 megapixel
2 1 available
2 2 available
2 3 3 megapixel
3 1 available
3 3 5 megapixel

i need to get ProductID when (FeatureID = 1 FeatureDescription = available) and (FeatureID = 3 FeatureDescription = 5 megapixel) and ... more
ProductID
1
3

select distinct a.productid from

(SELECT productid,featureid,featuredescription from ProductFeatures where featureid = 1 and featuredescription = 'available') A

inner join

(SELECT productid,featureid,featuredescription from ProductFeatures where featureid = 3 and featuredescription = '5 megapixel') B

on a.productid = b.productid

Try the above....

|||

JOIN the tables, and add your filtering criteria to the WHERE clause. Something like:

SELECT p.ProductID

FROM Product p

JOIN ProductFeatures pf

ON p.ProductID = pf.ProductID

JOIN Features f

ON f.FeatureID = pf.FeatureID

WHERE ( ( f.FeatureID = 3

AND pf.FeatureDescription = 'Available'

)

AND ( f.FeatureID = 5

AND pf.FeatureDescription = '5 megapixel'

)

)

There are 'variations on the theme' -but the general idea is that you have to link (JOIN) the tables and apply the filtering criteria to the resultset.

|||thanks it's working
|||

The general form of the query that solves this problem performs an operation called relational division. You can do that using a query like below which will give better performance and is more generic form.

Code Snippet

declare @.FeatureID1 int, @.FeatureDesc1 varchar(30), @.FeatureID2 int, @.FeatureDesc2 varchar(30);
set @.FeatureID1 = 1;
set @.FeatureDesc1 = 'available';
set @.FeatureID2 = 3;
set @.FeatureDesc2 = '5 megapixel';
select *
from Product as p
where exists(
select 1
from ProductFeatures as pf
where pf.ProductID = p.ProductID
and ((pf.FeatureID = @.FeatureID1 and pf.FeatureDescription = @.FeatureDesc1)

or (pf.FeatureID = @.FeatureID2 and pf.FeatureDescription = @.FeatureDesc2))
group by pf.ProductID
having count(*) = 2
);