Member Avatar for Szabi Zsoldos

Hi guys,

Trying to develop a custom facet like search in Mysql.
I have 3 tables for this:

1.Attribute class
2.Attribute class Name
3.Attribute class Value
    - attributeClassName Id
    - productId
4.Products

Each product has one attribute class
Each attribute class has many values

Each value is contained in a sepparate row in the AttributeClassValue table, how can i Filter by values in that table ?
Group Concat is not the best solution....

  • 1 Contributor
  • 1 Reply
  • 205 Views
  • 28 Minutes Discussion Span
  • Latest Post Latest Post by Szabi Zsoldos
Member Avatar for Szabi Zsoldos

Got my solution.

SELECT p.SKU,
  p.productName,
  MAX(IF(t.AttributeClassValueId = '1', t.AttributeClassValue, NULL)) AS Atribute1,
  MAX(IF(t.AttributeClassValueId = '2', t.AttributeClassValue, NULL)) AS Atribute2,
  MAX(IF(t.AttributeClassValueId = '3', t.AttributeClassValue, NULL)) AS Atribute3,
  MAX(IF(t.AttributeClassValueId = '4', t.AttributeClassValue, NULL)) AS Atribute4,
  MAX(IF(t.AttributeClassValueId = '5', t.AttributeClassValue, NULL)) AS Atribute5
FROM CatSys_products p
    LEFT JOIN CatSys_productAttributeClassValuesFromProducts t ON p.id = t.ProductId
    LEFT JOIN CatSys_productAttributeClassValues a ON t.AttributeClassValueId = a.id
GROUP BY p.SKU, p.productName
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.