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,

Answer 1

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