Converting two Mysql selects to one

Question

I have a little doubt about Mysql.

I have the following Mysql query:

sq1 = mysqli($conexao,"select * from tabela1");
$tr1 = mysqli_num_rows($sq1);

Then I make the famous bow:

for ($i=0;$i<$tr1;$i++)
{ 
$registro1 = mysqli_fetch_array($sq1);
$codigo1   = registro1['tabela1_codigo'];

From the codigo1, i make a new query to search for data on tabela2:

sq2 = mysqli_query($conexao, "select * from tabela2 where 
tabela2_estrangeira='$codigo1'");

sq2 = mysqli_query($conexao, "select * from tabela3 where 
tabela3_estrangeira='$codigo1'");

// faço um outro `for` aqui para listar os dados da tabela2:

// faço um outro `for` aqui para listar os dados da tabela3:
 ...

}

My question is the following: How can I optimize these queries using only one select?

Answer 1

You can do something like this:

$sql = 'select T1.* from tabela1 as T1
 LEFT JOIN tabela2 AS T2 ON T2.tabela2_estrangeira = T1.tabela1_codigo';

I did something generic because JOIN can be the right RIGHT JOIN or you may also want to use INNER or OUTER.

Finally, not to complicate too much, use LEFT JOIN as an example above.

Be aware that in the example I applied alias.

The Tabela1 uses T1 and the second table uses T2.

To access Tabela1 columns, do T1.nome_da_caluna

ex: T1.tabela1_codigo

Answer 2

In your case, as you are not injecting any code, I do not see the need to pass a variable at the risk of sql Injection for no reason.

/* 
  Isso "t1.*,t2.*,t3.*" é o mesmo que isso "*",
  mas se você, de repente, quiser separar os valores,
  basta criar um alias, tipo: t1.nome as nom_tabela1, t2.nome as nom_tabela2
*/
    $sql = mysqli_query($conexao,
           'SELECT t1.*,t2.*,t3.*
            FROM tabela1 t1
            LEFT JOIN tabela2 t2
            on(t1.tabela1_codigo=t2.tabela2_estrangeira)
            LEFT JOIN tabela3 t3
            on(t2.tabela2_estrangeira=t3.tabela3_estrangeira)
            WHERE 1');