dwhite365
asked on
AND child records
We have a table of products and a table of features. And then we have a three-field reference table that ties a product to a feature - one record per product/feature combo, with the productID as the key into the product table, and the feature ID as the key into the feature table - the third field is just an ID field.
My problem is to find the products that have all of features A, B, and C.
So how do I construct a query that finds the products that have a given set of features - an AND set if I just had a table whose record was a product ID field and a separate field for each feature. But with each feature in a separate record, an AND query won't work.
Do I create a temporary table populated by an OR query, and then query that table for products with a record count = the number of features in the OR query?
It is not possible to change the structure of the product or the feature table or of the product/feature reference table.
My problem is to find the products that have all of features A, B, and C.
So how do I construct a query that finds the products that have a given set of features - an AND set if I just had a table whose record was a product ID field and a separate field for each feature. But with each feature in a separate record, an AND query won't work.
Do I create a temporary table populated by an OR query, and then query that table for products with a record count = the number of features in the OR query?
It is not possible to change the structure of the product or the feature table or of the product/feature reference table.
If you have 32 tables
1 products to store products
2 features to store features
3 pro_feat_relation store features of each product
try query
select p.product_id from products as p
inner join pro_feat_relation as r on r.product_id=p.product_id
inner join features as f on f.feature_id=r.feature_id and f.feature_name='A' and f.feature_name='B' and f.feature_name='C'
1 products to store products
2 features to store features
3 pro_feat_relation store features of each product
try query
select p.product_id from products as p
inner join pro_feat_relation as r on r.product_id=p.product_id
inner join features as f on f.feature_id=r.feature_id and f.feature_name='A' and f.feature_name='B' and f.feature_name='C'
/*to find products having all the features A, B and C*/
select *
from products P
inner join features F
on F.product_id = P.product_id
where exists (select * from features A where a.product_id = p.product_id and a.features ='A')
and exists (select * from features B where B.product_id = p.product_id and B.features ='B')
and exists (select * from features C where C.product_id = p.product_id and C.features ='C')
/*to find products have any of the features A, B and C in column*/
select P.PRODUCT_NAME, P.PRODUCT_ID,
CASE WHEN (SELECT COUNT(*) FROM FEATURES A WHERE A.PRODUCT_ID = P.PRODUCT_ID AND A.FEATURES ='A') =0 THEN 'N' ELSE 'Y' END FEATUREA,
CASE WHEN (SELECT COUNT(*) FROM FEATURES B WHERE B.PRODUCT_ID = P.PRODUCT_ID AND B.FEATURES ='A') =0 THEN 'N' ELSE 'Y' END FEATUREB,
CASE WHEN (SELECT COUNT(*) FROM FEATURES C WHERE C.PRODUCT_ID = P.PRODUCT_ID AND C.FEATURES ='A') =0 THEN 'N' ELSE 'Y' END FEATUREC
FROM
PRODUCTS
The way I read the question you're "looking for all products with at least the following list of features: A, B, C" (products having A, B, C, and D should also be included).
The following query selects only the products with at least the features requested:
select
products.product_id
from products
where (select count(*)
from Features
inner join pro_feat_relation
on pro_feat_relation.product_ id = products.product_id
and pro_feat_relation.feature_ id = features.feature_id
where Feature_Name in ('A', 'B', 'C')) = 3
However... it uses a hard coded list of features and a count of features. You can switch this to a temp table (or some such) like so:
create table #RequestedFeatures
(
feature_name varchar(50) primary key
)
insert into #RequestedFeatures (feature_name) values('A')
insert into #RequestedFeatures (feature_name) values('B')
insert into #RequestedFeatures (feature_name) values('C')
select
products.product_id
from products
where (select count(*)
from Features
inner join pro_feat_relation
on pro_feat_relation.product_ id = products.product_id
and pro_feat_relation.feature_ id = features.feature_id
where Feature_Name in (select feature_name from #RequestedFeatures)) = (select count(*) from #RequestedFeatures)
drop table #RequestedFeatures
If you go the temp table route you might be better served by replacing the "feature_name in (...)" bit with a simple inner join to the temp table (add feature_id's to the temp table instead of feature names). Depending on the size of your database etc this change might improve query performance. Check the execution plan both ways to make a final determination.
Regards,
Rob
The following query selects only the products with at least the features requested:
select
products.product_id
from products
where (select count(*)
from Features
inner join pro_feat_relation
on pro_feat_relation.product_
and pro_feat_relation.feature_
where Feature_Name in ('A', 'B', 'C')) = 3
However... it uses a hard coded list of features and a count of features. You can switch this to a temp table (or some such) like so:
create table #RequestedFeatures
(
feature_name varchar(50) primary key
)
insert into #RequestedFeatures (feature_name) values('A')
insert into #RequestedFeatures (feature_name) values('B')
insert into #RequestedFeatures (feature_name) values('C')
select
products.product_id
from products
where (select count(*)
from Features
inner join pro_feat_relation
on pro_feat_relation.product_
and pro_feat_relation.feature_
where Feature_Name in (select feature_name from #RequestedFeatures)) = (select count(*) from #RequestedFeatures)
drop table #RequestedFeatures
If you go the temp table route you might be better served by replacing the "feature_name in (...)" bit with a simple inner join to the temp table (add feature_id's to the temp table instead of feature names). Depending on the size of your database etc this change might improve query performance. Check the execution plan both ways to make a final determination.
Regards,
Rob
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can join that back to the products table if you need to get details on the prods.
Could you make an example of what result you want?