SQL Select multiple column values

Question

I'm having difficulty with some SQL - and finding it hard to describe so please bear with me. I'm trying to select products that have an x number of correct features. To simplify things, the problem I'm having is with a few tables. The relevant information about my tables, is:

products(**product_id**)
features(**feature_id, product_id**, value_id)
featurenames(**feature_id**, name)
featurevalues(**value_id**, value)

I am trying to select all products that, for example, are for sex:male age:children, with age and sex being names in the featurenames table and male and children being values in the featurevalues table. This is part of another bigger query that gets all the products by category, which I haven't included as it will just complicate things. Thank you for your help in advance.

Stu · Accepted Answer · 2010-11-17 23:25:57Z

1

It's the table so nice, you join to it twice.

Select
  P.Product_ID
From
  Products P
  Inner Join Features F1 On P.Product_ID = F1.Product_ID
  Inner Join FeatureNames FN1 On F1.Feature_ID = FN1.Feature_ID And FN1.Name = 'sex'
  Inner Join FeatureValues FV1 On F1.Value_ID = FV1.Value_ID And FV1.Value = 'male'
  Inner Join Features F2 On P.Product_ID = F2.Product_ID
  Inner Join FeatureNames FN2 On F2.Feature_ID = FN2.Feature_ID And FN1.Name = 'age'
  Inner Join FeatureValues FV2 On F2.Value_ID = FV2.Value_ID And FV1.Value = 'children'

answered Nov 17, 2010 at 23:25

Stu

15.8k4 gold badges46 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harry M Over a year ago

Why thank you kind sir, I think I tried this before but somehow didn't get it correct.

Collectives™ on Stack Overflow

SQL Select multiple column values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related