CODEWITHSUNDEEP
phpmysqlsql
0x_Anakin
0x_Anakin

Reputation: 3269

Need to fetch all products that have specific features with mysql

Hello I have a table in some cms (products) that has 3 columns:

product_id feature_id value
1          1          blue
1          8          cotton
2          5          t-shirt
2          1          red
3          8          wool
1          9          large

Each product has its own id, also each product has a list of features (about 10 of them) each feature_id represent's a feature. Depending on the feature_id the "value" column has the value for that type of feature.

E.x. imagine clothes, 1 unique product_id -> 1 cloth -> many features / properites like color type of material price size etc etc.

Question is it possible to fetch 3 rows something like 

product_id value1 value2
1          blue   large
2          red    medium
3          green  xsmall
4          purple xlarge

where value1 is the string on the value column when products 1 feature_id = 1 and value2 is the string on the value column when products 1 feature_id = 9 (feature_id for size)

I cant figure out how to do this in one statement, without having to merge arrays in php.

Upvotes: 1

Views: 175

Answers (3)

0x_Anakin
0x_Anakin

Reputation: 3269

SELECT DISTINCT product_id AS book_id, (

SELECT value
FROM cscart_product_features_values
WHERE product_id = book_id
AND feature_id =  '1'
) AS publisher, (

SELECT value
FROM cscart_product_features_values
WHERE product_id = book_id
AND feature_id =  '8'
) AS author
FROM  `cscart_product_features_values` 
ORDER BY product_id

Well after some testing this works great :)

Upvotes: 0

jachguate
jachguate

Reputation: 17203

You can do it this way:

select p.product_id
       (select value
          from product_features pf
         where pf.product_id = p.product_id
           and pf.feature_id = 1) as feature_1
       (select value
          from product_features pf
         where pf.product_id = p.product_id
           and pf.feature_id = 9) as feature_9
  from p.products

The query is standard SQL, so any compliant database should accept it.

I'm assuming you have a normal design with a products and product_features table

Upvotes: 0

E_p
E_p

Reputation: 3144

Join will work: http://dev.mysql.com/doc/refman/5.0/en/join.html

SELECT * FROM tbl_Products 
JOIN tbl_Features ON tbl_Products.product_id = tbl_Features.product_id
WHERE tbl_Features.feature_id = ?

or

SELECT * FROM tbl_Products 
JOIN tbl_Features ON tbl_Products.product_id = tbl_Features.product_id
WHERE tbl_Features.feature_id in (1, 2, 3, 4)

Upvotes: 1

Related Questions