filter - select attribute mysql -

I have mysql table:

  ** product (id, name) ** 1 Samsung 2 Toshiba 3 Sony ** Specialty (id, name, guardian) ** 1 size 0 2 19 "1 3 17" 1 4 15 "1 5 colors 0 6 white 5 7 black 5 8 price 0 9 < 100 8 10 $ 100- $ 300 8 11 & $; $ 300 8 ** Attribute 2 Product (ID, generated, attribution) ** 1 1 2 2 1 6 3 2 2 4 2 7 5 3 3 6 3 7 7 1 9 8 29 9 10 10  

and they are listed as:

  ** size ** - 19 "(2) - 17" (1) - 15 "(0) ** color ** - white (1) - black (2) ** value ** - & lt; $ 100 (1) - $ 100- $ 300 (1) - & gt; $ 300 (1)  

Please help the mysql query to list the attribute name and calculate the number that this feature has. Example: When Select Size 19 "(attribute.id 2)

  ** Size ** - 19" ** Color ** - White (1) - Black (1) * * Value ** - & lt; $ 100 (1) - $ 100- $ 300 (1)  

This feature asks 2 products >> select manufacturers >> next to select other attributes that are productive and The name of the attribute, the name of that property is now ... (like Magento)

thanks,

I modified the query that should be based on your update:

  SELECT attribute.name AS attributename, COUNT (*) AS from numofproducts Insert product INNER attribute2prod Uct ON attribute2product.productid = product.id Join INNER attribute Attribute.id = attribute2product.attributeid WHERE product.id IN (SELECT p.id as a product from P INNER attribute2product AS a2p at a2p.productid = p.id WHERE a2p.attributeid = 2) by attribute. Group, attribute.name;  

Based on your above data I found:

  attributename numofproducts 19 "2 white 1 black 1 $ 100 2  

For many features ():
_{I have removed the product table, because it is not needed here}

  select attribute.name AttributeName, COUNT (aS *) as the attribute2product interiors on attribute.id = attribute2product.attributeid characterized Join selection of numofproducts where attribute2product.productid (o.productid (from productid selection att as (2 select UNION Select all 6 Join attribute2product at V Attributeid & gt; = at and attrified & lt; = et) o Grouped by o.producted heaving COUNT (*) = 2) attribute.id by group; id, attribute.name  

2 , 6 see 19 " and white , res pectively. COUNT (*) = 2 to match the two attributes can be added by adding more features to the nested derived table:

  Unias Select All & Lt; Attributeid & gt; As att  

As expected, the results of the query:

  AttributeName numofproducts 19 "1 White 1 & LT; $ 100 1