CODEWITHSUNDEEP
mysql
Harry M
Harry M

Reputation: 195

SQL Select multiple column values

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.

Upvotes: 0

Views: 229

Answers (1)

Stu
Stu

Reputation: 15769

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'

Upvotes: 1

Related Questions