PHP - How to make left Join with more than one record in the other tables not return repeated values in the columns?

Asked

Viewed 879 times

2

I need to perform an SQL query in PHP, where I need to create a table from 9 other tables, joining all the columns with a LEFT JOIN, and with the 'Client' field as key between the tables. However, in each table there is more than one record of the same key, that is, more than one row with the same number of 'Client', and with this the query is returning me repeated values, changing only the values of the columns that are of different data. I would like these repeated values of the columns, only to appear once, and in the others, to return me with NULL. I will put just below an image showing how it is being returned to me the values. Does anyone have any idea how to fix this? Thank you so much!

Follow the query code:

    SELECT * 
      FROM completo_sem_saldo 
 LEFT JOIN posicao_contabil ON (completo_sem_saldo.Cliente = posicao_contabil.Cliente) 
 LEFT JOIN saldo_analitico ON (completo_sem_saldo.Cliente = saldo_analitico.Cliente) 
 LEFT JOIN titulos_em_ser ON (completo_sem_saldo.Cliente = titulos_em_ser.Cliente

HOW ARE YOU RETURNING: valores retornados

HOW I WANT YOU TO RETURN:

inserir a descrição da imagem aqui

COMPLETE CODE OF THE TABLE: 

$query1 = mysqli_query($con, "SELECT * FROM completo_sem_saldo as a LEFT JOIN posicao_contabil as b ON (a.Cliente = b.Cliente_Posicao) LEFT JOIN saldo_analitico as c ON (a.Cliente = c.Cliente_Saldo_Analitico) LEFT JOIN titulos_em_ser as d ON (a.Cliente = d.Cliente_Titulos_Em_Ser) ) as e");

$num = mysqli_num_rows($query1);
$resul1 = mysqli_fetch_assoc($query1);
$arquivo = 'exemplo.xls';
$tabela = '<table border="1">';
$tabela .= '<tr>';
$tabela .= '<td colspan="2">Formulario</tr>';
$tabela .= '</tr>';
$tabela .='<tr>';
$tabela .='<td><b>Nome</b></td>';
$tabela .='<td><b>Sld_dev_ctbl</b></td>';
$tabela .= '<td><b>Saldo</b></td>';
$tabela .='<td><b>Vlr_atual</b></td>';
$tabela .='</tr>';

while($resul1 = mysqli_fetch_assoc($query1)) {

$nome = $resul1['Nome'];
$sld_dev_ctbl = $resul1['Sld_dev_ctbl'];
$saldo = $resul1['Saldo'];
$vlr_atual = $resul1['Vlr_atual'];

$tabela .= '<tr>';
$tabela .= '<td>'.$nome.'</td>';
$tabela .= '<td>'.$sld_dev_ctbl.'</td>';
$tabela .= '<td>'.$saldo.'</td>';
$tabela .= '<td>'.$vlr_atual.'</td>';
$tabela .= '</tr>';
}
$tabela .= '</table>';
php mysql query join

  • I updated the code in the description.

  • 2

    Already tested a GROUP BY ?

  • Actually already, but it didn’t hurt. What I really need is for Join to return me only the values found in the tables, and not repeat the other previous values. :/

  • the columns Sld_dev_ctbl and Saldo always have the same value? Appearing the sum of the Vlr_atual for a particular client resolves?

  • In fact only the 'Balance' column will always have the same value for a customer. The columns 'Sld_dev_ctbl' and 'Vlr_current' will always have different values, when they appear more than once to a client.

  • Why don’t you do this treatment in the application instead of treating it by sql? I don’t know any other way but to apply pivot... In short lines, I think your select is right.. you have to handle in the application what you want.

  • 1

    Post an image of what you want to return and what is returning. I could not understand exactly what you want. : ( If it is to undo the repetitions you have to see what is equal, humanly. It would just add this: GROUP BY Nome

  • I just put another image showing how I really want the result of the query to be. You may notice that the values repeated in the first image, do not repeat in the second, appear only once.

  • you tried to use "select distinct ..."?

  • In fact, DISTINCT brings back the values that are duplicated, and that’s not quite what I need. I need it to take duplicate values, delete (NULL), and only leave the first record.

  • Is that what you want? http://stackoverflow.com/questions/14286417/mysql-join-return-null-for-duplicate-results-in-left-table

Show 6 more comments

2 answers

0

This is not an SQL problem, the moment you make a Join you are generating a "Cartesian product".

What I can suggest to you is during the processing of the data, display only the information that you do not want to repeat on the first occasion of the customer ID.

I don’t know what your code is, but here’s a generic example:

<?php
$rows = array();

$row['cliente_id'] = 1;
$row['nome'] = 'fulano';
$row['saldo'] = 10;
$row['valor'] = 1;
$rows[] = $row; 

$row['cliente_id'] = 1;
$row['nome'] = 'fulano';
$row['saldo'] = 10;
$row['valor'] = 2;
$rows[] = $row; 

$row['cliente_id'] = 1;
$row['nome'] = 'fulano';
$row['saldo'] = 10;
$row['valor'] = 3;
$rows[] = $row; 

$row['cliente_id'] = 2;
$row['nome'] = 'fulano';
$row['saldo'] = 10;
$row['valor'] = 1;
$rows[] = $row; 

$row['cliente_id'] = 2;
$row['nome'] = 'fulano';
$row['saldo'] = 10;
$row['valor'] = 2;
$rows[] = $row; 

echo "<table>";
echo "<tr>";
echo "<th>ID</th>";
echo "<th>Nome</th>";
echo "<th>Saldo</th>";
echo "<th>Valor</th>";
echo "</tr>";

$last_id = null;
foreach($rows as $row){
    echo "<tr>";
    echo $last_id != $row['cliente_id'] ? "<td>$row[cliente_id]</td>" : "<td></td>";
    echo "<td>$row[nome]</td>";
    echo $last_id != $row['cliente_id'] ? "<td>$row[saldo]</td>" : "<td></td>";
    echo "<td>$row[valor]</td>";
    echo "</tr>";
    $last_id = $row['cliente_id'];
}
echo "</table>";

If you post your code, as is mounting the table we can help in the real case.

  • I just updated in the description the complete code. If you can help me brother, I will be immensely grateful. Take a look at the code.

0

You can use the command DISTINCT after the operator SELECT and call the field that you do not want to be repeated, usually you do this in the ID, so it will not match all entries of the Cartesian plan.

Example in W3schools (English)
Example in Mysql Tutorial (English)

Browser other questions tagged php mysql query join

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