Pick up data using Join in two table 1 for many

Asked

Viewed 543 times

0

good morning,

I need to perform a select of two tables that are related 1 to many:

Table 1: products Table 2: price group

I need to get all prices from the price group table, which may have 1 or more.

In my case of the select below it even takes the most prices repeats all the data as if it were another product, as I can do so that each products already comes with all the prices corresponding to it?

I am using Codeigniter, below the select I am currently using.

$this->db->select('produtos.*, grupo_preco_produtos.grupo_preco_venda');
$this->db->from('produtos');
$this->db->join('grupo_preco_produtos', 'grupo_preco_produtos.id_produto = produtos.id');
$this->db->where(array('produtos.id_empresa' =>$id_empresa, 'produtos.del' => 0, 'produtos.tipo_produto' => 1,));
$query = $this->db->get();
return $query->result();
mysql sql database codeigniter join
  • 1

    Have you thought about how you would use it later? Sometimes what you want to do is not the best option and the people here could help you with a better solution.

  • 1

    You want to bring them separated by comma?

  • So Denis I need to put together a list of the products and their prices, I didn’t want to have to have each line have to connect to the database to get the data, I think it might overload the database, because it is an Pr that will have several clients or you think that does not burden the bank

  • Sorack, it can be yes and because at the time of listing I can separate.

  • Yes Denis, I know this may be that what I am doing may not be the best option in case friends have another better option please can pass me without any problem

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

Show 1 more comment

2 answers

1

In this case you use the GROUP_CONCAT.

Your Query would look like this:

SELECT produtos.*, GROUP_CONCAT(grupo_preco_produtos.grupo_preco_venda SEPARATOR ', ')
FROM produtos
INNER JOIN grupo_preco_produtos ON grupo_preco_produtos.id_produto = produtos.id
WHERE produtos.id_empresa = '.$id_empresa.' AND produtos.del = 0 AND produtos.tipo_produto = 1
GROUP BY produtos.id;

Another tip I give you is to name your tables when using Join’s, that same query would look like this:

SELECT a.*, GROUP_CONCAT(b.grupo_preco_venda SEPARATOR ', ')
FROM produtos a
INNER JOIN grupo_preco_produtos b ON b.id_produto = a.id
WHERE a.id_empresa = '.$id_empresa.' AND a.del = 0 AND a.tipo_produto = 1
GROUP BY a.id;

1

You can add a GROUP_CONCAT at your query:

$this->db->select('produtos.*, GROUP_CONCAT(grupo_preco_produtos.grupo_preco_venda SEPARATOR ",") as grupo_preco_venda');
$this->db->group_by('produtos.id');

  • In this way he did not, so he granted all prices and listed only one product. and actually have list all products with the prices relating to it.

  • @Claytoneduardomergulhão I made a change, take a look there

Browser other questions tagged mysql sql database codeigniter join

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