Consult with INNER JOIN and choose the loop reference

Asked

Viewed 83 times

2

Guys, I’m looking at two tables with INNER JOIN thus:

if (! $db->Query("select * from cad_produto inner join cad_variacoes on cad_variacoes.id_produto = cad_produto.id")) 

$db->Kill(); 

while (! $db->EndOfSeek()) {  
  $row = $db->Row();

  $titulo = $row->titulo; //TABELA 1

  $valor = $row->valor; //TABELA 2
  $quantidade = $row->quantidade; //TABELA 2
  $id = $row->id_produto; //TABELA 2
}

TABLE 1: 

 +----------------------------+
 |           PRODUTO          |
 +------------+---------------+
 | ID         | TITULO        |
 | 1          | arroz         |
 |            |               |
 |            |               |
 |            |               |
 +------------+---------------+

TABLE 2:

 +---------------------------+
 |         ATRIBUTO 1        |
 +------------+--------------+
 | ID_PRODUTO | VALOR        |
 | 1          | 10           |
 |            |              |
 |            |              |
 |            |              |
 +------------+--------------+

 +---------------------------+
 |         ATRIBUTO 2        |
 +------------+--------------+
 | ID_PRODUTO | QUANTIDADE   |
 | 1          | 5            |
 | 1          | 6            |
 |            |              |
 |            |              |
 +------------+--------------+

I need that at the time of loop appear only the loop AS A REFERENCE TO THE NUMBER OF ROWS IN TABLE 1 AND NOT IN TABLE 2, but it’s happening the other way. It’s coming like this:

ID(dado da tabela 2)    TITULO    VALOR QUANTIDADE
1                       ARROZ      10       5

ID(dado da tabela 2)    TITULO    VALOR QUANTIDADE
1                       ARROZ      10       6

I need you to come only 1 row and with your respective attributes.

UPDATE

It was supposed to look like this, taking a random value from table 2:

ID(dado da tabela 2)    TITULO    VALOR QUANTIDADE
1                       ARROZ      10       5

I can not put LIMIT 1, because when there are two products will be like this:

ID(dado da tabela 2)    TITULO    VALOR QUANTIDADE
1                       ARROZ      10       5

ID(dado da tabela 2)    TITULO    VALOR QUANTIDADE
2                       FEIJAO     11       1
php sql loop

  • Puts the title outside the while.

  • But when there are two rows in table 1, you will have two products consulted, two titles, one in each row.

  • Oh understood. It was bad. But in this case you want it to appear as ? Can you reproduce in your post ? Is it to add up the amount ? Why just like that to appear on one line. sum(quantidade) and group by id

  • 1

    @Diegosouza, I will edit...see the question change below.

  • It’s a little complicated to understand your logic. You can’t put LIMIT 1 if it doesn’t happen what you’ve shown there. You want a random row to appear with a table 2 value of only 1 product ?

  • This, but by product, summarizing the loop is based on table 1 with the data in table 2.

  • Have you tried using LEFT JOIN instead of INNER JOIN?

  • already, Igor silva ...........

Show 3 more comments

1 answer

1


Just "count" the table Ids you want, then you can do something like this:

SELECT cad_produto.*,cad_variacoes.*, count(DISTINCT cad_produto.id) qtd
FROM cad_produto
INNER JOIN cad_variacoes ON cad_variacoes.id_produto = cad_produto.id

Where Count(DISTINCT cad_product.id) Qtd will count the unique Ids, even if the JOIN make him repeat.

Browser other questions tagged php sql loop

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