How to consult and return all the information about a product?

Question

How to consult and return all the information about a product?

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (2 votes)

1

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

The tbl_cor in this case is to register the product with all its color possibilities. This will be done in another form where a drop will return all the products for the choice of one of them and later register of the 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:

% w / w Kombe of produto utility available in categoria green, blue, white, yellow . How to return through a SQL query this data?

php mysql query

asked by anonymous 26.11.2014 / 11:49

3 answers

3

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;

26.11.2014 / 12:08

2

Good morning, I did not understand if you just want an appointment or something more complicated, I made this query 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'

Hope it helps, anything comments.

26.11.2014 / 12:07

DateTime Picker in Portuguese Are there online programming learning environments with hands-on exercises and challenges for C #?

score 2 · Accepted Answer

You can use the GROUP_CONCAT function 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 the 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 have helped.