How to return more than one column from the same table?

Asked

Viewed 537 times

3

With this SQL I can in my database select and group products by variations that would be in this case different attributes for the same product.

Thus, when generating the product view, I have a dropdown in the product with all attributes that the customer can choose in a greater number of possibilities for the same product, for example a car that has several colors.

Here is the SQL:

$BuscaProdutosAtivos = $pdo->prepare("SELECT tbl_produtos.*, tbl_categorias.*,
                                      GROUP_CONCAT(tbl_variacoes.valor) AS Variacoes FROM tbl_variacoes
                                      INNER JOIN tbl_produtos ON tbl_produtos.id = tbl_variacoes.cod_prod  
                                      INNER JOIN tbl_categorias ON tbl_produtos.prod_categoria = tbl_categorias.cat_id
                                      GROUP BY (tbl_produtos.id)");

With this SQL I return from tbl_variacoes the column valor. I’d like to return valor, dimensao, quantidade because these three information will be concatenated inside the dropdown forming a variation. How can I do it using this SQL?

I figured if I could use several GROUP_CONCAT but searching in some places I saw that it should not be done that way.

inserir a descrição da imagem aqui

php mysql query
  • 2

    Every time you wear one GROUP_CONCAT for something that needs to break later, 100 people die of Ebola in Africa.

  • Seriously now, it may be the case to make separate queries to catch these "variations". To answer more precisely I would need to see an example of what you want to put in such a dropdown.

  • Or simply concatenate these things in PHP, not Mysql. You can’t quite understand the question.

1 answer

2


I didn’t really understand what you wanted to return, but here are two examples:

SELECT tbl_produtos.*, tbl_categorias.*, 
tbl_variacoes.valor || tbl_variacoes.dimensao || tbl_variacoes.quantidade AS Variacoes 

FROM tbl_variacoes

INNER JOIN tbl_produtos 
ON tbl_produtos.id = tbl_variacoes.cod_prod  

INNER JOIN tbl_categorias 
ON tbl_produtos.prod_categoria = tbl_categorias.cat_id

Or

SELECT tbl_produtos.*, tbl_categorias.*, 
tbl_variacoes.valor, tbl_variacoes.dimensao, tbl_variacoes.quantidade

FROM tbl_variacoes

INNER JOIN tbl_produtos 
ON tbl_produtos.id = tbl_variacoes.cod_prod  

INNER JOIN tbl_categorias 
ON tbl_produtos.prod_categoria = tbl_categorias.cat_id

Browser other questions tagged php mysql query

You are not signed in. Login or sign up in order to post.