CODEWITHSUNDEEP
mysqlrelational-databaseinner-join
kuzey beytar
kuzey beytar

Reputation: 3227

Product feature selector using MySQL

As you see below, product (id 100) related with Man, S and M variants.

Features table:

 feature_id      name
------------  ----------
     1          Gender
     2           Size

Feature variants table:

 variant_id      feature_id        name
------------    -------------    --------
     1                1             Man
     2                1            Woman
     3                2              S
     4                2              M
     5                2              L

Product Feature relation table:

 product_id      feature_id        variant_id
------------    -------------     ------------
    100               1                1
    100               2                3
    100               2                4

How can I relate these three variants with product using MySQL command? I really didn't do this.

Upvotes: 0

Views: 153

Answers (2)

Adam Dymitruk
Adam Dymitruk

Reputation: 129526

First, you are repeating some information unnecessarily. You don't need feature_id in your product feature relation table. That can come from your feature variants table. If you are adding new product attributes, you'll have to duplicate this information. Also, you can get into an inconsistent state. If some of your reports rely on feature id from one table and another uses the other table, and you forget to update one of them, you can have a report that is still showing the wrong information.

To answer your quesion, you simply join across the tables.

select pf.product_id, f.name, fv.name 
  from       productfeatures pf 
  inner join featurevariants fv   on pf.variant_id = fv.variant_id
  inner join features             on fv.feature_id = f.feature_id

You don't have a product table in your example, but if you did, you could also join that and get the name of the product instead of the id, if you wanted to.

hope this helps!

Upvotes: 1

calumbrodie
calumbrodie

Reputation: 4792

You have not shown the 'product' table but I'm assuming there is one.

SELECT * FROM product p INNER JOIN product_feature_relation rel ON p.product_id = rel.product_id INNER JOIN feature_variants fv ON rel.variant_id = fv.variant_id INNER JOIN features f ON fv.feature_id = f.feature_id

The chances are the results that this query will give may not be useful if you are going to iterate and act on them straight away. It would be better to have 2 rows representing unique sizes, rather than three (2 sizes + gender) - but you didn't specify that in the question so I'm not sure that you need it.

Upvotes: 1

Related Questions