conditions (where) mysql

Question

Table products:

id|name
-------
1 |computer
2 |microwave
3 |transl

Table product_features:

feature          | id_product | feature_value
------------------------------------
count_of_buttons | 1          | 1
count_of_buttons | 2          | 2
count_of_buttons | 3          | 1
color            | 1          | white
color            | 2          | white
color            | 3          | black

Pls, how to get all white products with one button? Thank you very much!

score 4 · Accepted Answer · edited Jul 15 '11 at 18:00

4

select product.*

from product
  join product_features as buttons
    on buttons.id_product = product.id
  join product_features as color
    on color.id_product = product.id

where buttons.feature_value = '1'
  and buttons.feature = 'count_of_buttons'

  and color.feature_value = 'white'
  and color.feature = 'color';

edited Jul 15 '11 at 18:00

ypercubeᵀᴹ

113,259
19
174
235

answered Jul 15 '11 at 16:27

jakber

3,549
20
20

redbmk · Answer 2 · 2011-07-15T19:20:51.663

1

select
    p.id
    p.name
from
    products p
    join (select * from product_features where feature = 'color') colors on (p.id=colors.id_product)
    join (select * from product_features where feature = 'count_of_buttons') buttons on (p.id=buttons.id_product)
where
    colors.feature_value = 'white'
    and buttons.feature_value = 1

You might consider reorganizing the product_features table so that you have a separate column for each feature (i.e. a color column and a count_of_buttons column) so that you have one row for each product. In fact it could all be in the products table.

edited Jul 15 '11 at 19:20

answered Jul 15 '11 at 17:34

redbmk

4,687
3
25
49

the `product_features` table is an example of the **EAV** (Entity-Attribute-Value) approach. There is no need for window functions. Queries usually need many joins (to the same table) with this approach. – ypercubeᵀᴹ Jul 15 '11 at 17:57
Wouldn't that approach be costly, especially for large tables? RE windowing functions it seemed to make sense at first but now I can't think how they would be handy -- editing answer. – redbmk Jul 15 '11 at 19:20
The problem with EAV is not the cost of JOINs. Having many joins in a query may be slow but it may be quite fast with indexing. There are other issues, like when you have to store many different data types in the same column. There are also referential integrity constraints that are hard (if not impossible) to enforce. – ypercubeᵀᴹ Jul 15 '11 at 19:24
Check this discussion: http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question – ypercubeᵀᴹ Jul 15 '11 at 19:26
How can we prevent a row with `'count_of_buttons', 'purple'` or a `'color', '73'` ? – ypercubeᵀᴹ Jul 15 '11 at 19:31

score -1 · Answer 3 · answered Jul 15 '11 at 16:27

-1

SELECT p.id FROM products p JOIN product_features pf ON p.id = pf.id WHERE pf.feature_value = 'white' AND pf.count_of_buttons = 1

answered Jul 15 '11 at 16:27

Duffp

5,878
2
15
16

1

Won't work because the `color` and `count_of_buttons` are different rows in the same column, not different columns in the same row. – Michael Berkowski Jul 15 '11 at 16:34

Londeren · Answer 4 · 2011-07-15T16:39:38.100

-1

select p.id, p.name from products p inner join product_features ON p.id=id_product where feature_value='white' and feature='color' and count_of_buttons=1

edited Jul 15 '11 at 16:39

answered Jul 15 '11 at 16:27

Londeren

3,202
25
26

The OP asked for white products with only one button. This only gives white products. – Michael Berkowski Jul 15 '11 at 16:33
@ypercube It's been edited since my comment. Still won't work though. – Michael Berkowski Jul 15 '11 at 17:59

conditions (where) mysql

4 Answers4