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 I WANT YOU TO RETURN:
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>';
I updated the code in the description.
– Marconi Rodrigues
Already tested a GROUP BY ?
– Junior Moreira
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. :/
– Marconi Rodrigues
the columns
Sld_dev_ctbl
andSaldo
always have the same value? Appearing the sum of theVlr_atual
for a particular client resolves?– Marllon Nasser
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.
– Marconi Rodrigues
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.– Marllon Nasser
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
– Inkeliz
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.
– Marconi Rodrigues
you tried to use "select distinct ..."?
– Ruggi
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.
– Marconi Rodrigues
Is that what you want? http://stackoverflow.com/questions/14286417/mysql-join-return-null-for-duplicate-results-in-left-table
– Daniel Omine