• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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.
0
dwhite365
Asked:
dwhite365
1 Solution
 
Z03niECommented:
I'm not clearly understand what you want.
Could you make an example of what result you want?

0
 
HuyBDCommented:
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'
0
 
imran_fastCommented:

/*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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rmacfadyenCommented:
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
0
 
Scott PletcherSenior DBACommented:
SELECT productID
FROM [reference]
WHERE featureID IN (...list of feature ids to match...)
GROUP BY productID
HAVING COUNT(*) = ...count of feature ids to match...
0
 
Scott PletcherSenior DBACommented:
You can join that back to the products table if you need to get details on the prods.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now