How to consult and return all product information?

Question

I have the following problem and I will ask a question in a didactic way so that it can be useful to all users who may need it. I want to conduct a consultation SQL that meets all requirements and returns values.

To tbl_cor In this case it is to register the product with all its possibilities of colors. This will be done in another form where a drop will return all products for the choice of one and later registration of possible colors.

tbl_produto
    id_prod
    nome_prod
    preco_prod
    cat_prod      <----- id da categoria
    datacad_prod

tbl_categoria
    id_cat
    nome_cat      

tbl_cor
    id_prod
    cor_prod      <----- string de um dropdown do form

Example:

The produto is Kombe of categoria utility vehicle available in cores green, blue, white, yellow. How to return through a query SQL this data?

Answer 1

The following query returns all product information, with its colors and category.

SELECT *
FROM tbl_produto AS produtos,
     tbl_categ AS categorias,
     tbl_cor AS cores
WHERE produtos.cat_prod = categorias.id_cat AND
      produtos.id_prod = cores.id_prod
ORDER BY nome_prod;

Answer 2

Good morning, I didn’t understand if you just want a consultation or something more complicated, I made this consultation here:

SELECT nome_prod, nome_cat, cor_prod
FROM tbl_produto
INNER JOIN tbl_categoria
ON tbl_produto.cat_prod = tbl_categoria.id_cat
INNER JOIN tbl_cor
ON tbl_produto.id_prod = tbl_cor.id_prod
WHERE tbl_produto.nome_prod = 'KOMBE'

I hope it helps, anything comment.

Answer 3

you can use the function GROUP_CONCAT to return more than one record in a single column. Since you have multiple colors for a single product instead of returning a row for each color, you may be returning only one row with a column containing all colors.

SELECT
   tbl_produto.nome_prod,
   tbl_categoria.nome_cat,
   GROUP_CONCAT(tbl_cor.cor_prod) AS Cor
FROM tbl_cor
INNER JOIN tbl_produto ON tbl_produto.id_prod = tbl_cor.id_prod  
INNER JOIN tbl_categoria ON tbl_produto.cat_prod = tbl_categoria.id_cat
WHERE tbl_produto.nome_prod = 'KOMBE'
GROUP BY (tbl_produto.id_prod, tbl_categoria.id_cat)

I hope I’ve helped.