Return Attribute group of a specific category

Question

I have 3 tables: (http://sqlfiddle.com/#! 9/08b79f)

tb_attributes, tb_atr_to_prod, tb_atr_to_cat.

Table TB_ATRIBUTOS has all parent and child attributes. The TB_ATR_TO_PROD Table has all attributes with their respective products. Table TB_ATR_TO_CAT has the category code that represents a certain group of attributes. I need to create a list of all the attributes of a certain category, similar to this:

<h3>Cor</h3>
<ul>
  <li>Verde</li>
  <li>Azul</li>
  <li>Preto</li>
</ul>

<h3>Cone</h3>
<ul>
  <li>2.500M</li>
  <li>3.000M</li>
  <li>1.000M</li>
</ul>

Answer 1

First I ran this query to bring up your full list and analyze your request:

SELECT DISTINCT
    prd.id_prod as ID_PRODUTO,
    att.id as ID_ATRIBUTO,
    att.id_pai,
    att.nome_atributo,
    prd.id AS ID_TAB_PRD
FROM
    tb_atributo att
        LEFT JOIN
    tb_atr_to_prod prd ON (prd.id_atr = att.id)
WHERE
    prd.id IS NOT NULL

GROUP BY att.id , prd.id_prod

However, to do what you want, you need to use a technique called PIVOT:

Here’s a simple example

And here an example, more complex

Answer 2

Follow new select, with the restrictions you reported in the comments. See if it solves your question.

SELECT r.*, cat.id_cat
  FROM (  SELECT a.id AS id_pai,
                 '1 - Categoria' AS rotulo,
                 a.nome_atributo AS valor
            FROM tb_atributo a
           WHERE id_pai = 0
           UNION
           SELECT b.id_pai, 
                 '2 - Atributo' AS rotulo,
                  b.nome_atributo AS valor
             FROM tb_atributo b
            WHERE b.id_pai <> 0  ) AS r
 INNER JOIN tb_atr_to_cat cat ON r.id_pai = cat.id_atr
 INNER JOIN (SELECT DISTINCT 
                    a.id_pai
               FROM tb_atr_to_prod p
              INNER JOIN tb_atributo a ON p.id_atr = a.id) ap ON r.id_pai = ap.id_pai
 WHERE cat.id_cat = 115
 ORDER BY 1, 2, 3  

Unfortunately I could not test on sqlfiddle. Return me after your tests.

PREVIOUS ANSWER

Friend, follow a solution that returns a list as your example:

SELECT r.*
  FROM (  SELECT a.id,
                 '1 - Categoria' AS rotulo,
                 a.nome_atributo AS valor
            FROM tb_atributo a
           WHERE id_pai = 0
           UNION
           SELECT b.id_pai, 
                 '2 - Atributo' AS rotulo,
                  b.nome_atributo AS valor
             FROM tb_atributo b
            WHERE b.id_pai <> 0  ) AS r
 ORDER BY 1, 2, 3   

Just use the column rotulo of SELECT and assemble a display logic on your page PHP.

I hope I’ve helped.