select attribute mysql

Question

I have there mysql table:

**product (id,name)**
1  Samsung
2  Toshiba
3  Sony

**attribute (id,name,parentid)**
1 Size 0
2 19" 1
3 17" 1
4 15" 1
5 Color 0
6 White 5
7 Black 5
8 Price 0
9 <$100 8
10 $100-$300 8
11 >$300 8

**attribute2product (id,productid,attributeid)**
1 1 2
2 1 6
3 2 2
4 2 7
5 3 3
6 3 7
7 1 9
8 2 9
9 3 10

And listed them like:

**Size**
-- 19" (2)
-- 17" (1)
-- 15" (0)

**Color**
-- White (1)
-- Black (2)

**Price**
-- <$100 (1)
-- $100-$300 (1)
-- >$300 (1)

Please help me the mysql query to list the attribute name and count the number product that this attribute have. EG: When select Size 19" (attribute.id 2)

**Size**
-- 19"

**Color**
-- White (1)
-- Black (1)

**Price**
-- <$100 (1)
-- $100-$300 (1)

This will query to attribute2product >> select the productid >> next query to select other attribute of that productid and display the attribute name, number of prod that attribute name now have... (Like Magento)

Thanks,

@Chris Chua: I want to learn the mysql query (select, join, EAV table..) listing like Magento's products attribute filter. — Viet Tran
– Viet Tran, Commented May 17, 2010 at 17:00

sirhc · Accepted Answer · 2010-05-18 15:33:43Z

3

I've modified the query. This should be what you based on your updates:

SELECT attribute.name AS attributename, COUNT(*) AS numofproducts FROM product 
  INNER JOIN attribute2product ON  attribute2product.productid = product.id
  INNER JOIN attribute ON attribute.id = attribute2product.attributeid
  WHERE product.id IN 
    (
     SELECT p.id FROM product AS p
     INNER JOIN attribute2product AS a2p ON a2p.productid = p.id
     WHERE a2p.attributeid = 2
    )
  GROUP BY attribute.id, attribute.name;

Based on your above data I got:

  attributename   numofproducts
       19"              2
      White             1
      Black             1
      <$100             2

For multiple attributes (based a more knowledgeable expert Quassnoi's blog article) :
_{I've removed product table since it's not needed here}

SELECT attribute.name AS attributename, COUNT(*) AS numofproducts
FROM attribute2product
  INNER JOIN attribute ON attribute.id = attribute2product.attributeid
  WHERE attribute2product.productid IN (
      SELECT o.productid
      FROM (
        SELECT productid 
        FROM (
          SELECT 2 AS att
          UNION ALL
          SELECT 6 AS att
          ) v
        JOIN attribute2product ON attributeid >= att AND attributeid <= att
        ) o
      GROUP BY o.productid
      HAVING COUNT(*) = 2
      )
  GROUP BY attribute.id, attribute.name

2, 6 refer to 19" and White, respectively. COUNT(*) = 2 is to match 2 attributes. More attributes can be added by appending the following to nested derived table:

          UNION ALL
          SELECT <attributeid> AS att

As expected the result from the query:

  attributename   numofproducts
       19"              1
      White             1
      <$100             1

edited May 18, 2010 at 15:33

answered May 17, 2010 at 17:02

sirhc

6,0872 gold badges28 silver badges29 bronze badges

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

8 Comments

Trey Hunner Over a year ago

That first inner join should be on attribute2product.productid = product.id

Viet Tran Over a year ago

Cannot filter when select attribute with your query, Chris Chua. It will display the name of attribute we selected. Anyway, i've edited the Question and try to explain to you. Please help!

sirhc Over a year ago

I missed out an ON in my code, just added it in. It should return both the name of the attribute selected as well as the number of products with that attribute. Could you post your result here if you are still having problems?

Viet Tran Over a year ago

My result shows: Size -- 19" (2) Color -- 19" (2) Price -- 19" (2)

sirhc Over a year ago

I've modified my answer based on your new information. Please take a look. :)

|

Collectives™ on Stack Overflow

select attribute mysql

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related