learningbyexample learningbyexample - 6 months ago 14
SQL Question

How do I do a distinct on a join query using codeigniter or just to omitt duplicates

I have this code that does outputs repeated rows but when I look for them individually in the database only 3 rows apear but when I use join 6 of them come out and 3 of them are repeated. How do I avoid this or how do I omit the duplicates.

<?php
class Joins_model extends CI_Model{
private $table = 'cm_proveedor, cm_compras, cm_valuacion, cm_valuacionr, cm_nomina, cm_empleado';
function _construct(){
parent::Model();
}

function get_reg($data){
$this->db->distinct();
$this->db->select('
cm_proveedor.nombre,
cm_valuacion.mano_obra,
cm_valuacion.refaccion,
cm_valuacionr.refaccion,
cm_valuacion.costoHojalateria,
cm_valuacion.costoPintura,
cm_valuacion.costoMecanica,
cm_valuacion.pv_hojalateria,
cm_valuacion.pv_pintura,
cm_valuacion.pv_mecanica,
cm_valuacion.pc_hojalateria,
cm_valuacion.pc_pintura,
cm_valuacion.pc_mecanica,
cm_valuacion.tipo,
cm_valuacion.hojalateria,
cm_valuacion.pintura,
cm_valuacion.mecanica,
cm_valuacion.tipo_r,
cm_empleado.nombre,
cm_compras.precio
');

$this->db->from('cm_proveedor');
$this->db->join('cm_compras','cm_proveedor.id = cm_compras.id_proveedor');
$this->db->join('cm_valuacion','cm_compras.id_siniestro = cm_valuacion.id_siniestro');
$this->db->join('cm_valuacionr','cm_valuacion.id_siniestro = cm_valuacionr.id_siniestro');
$this->db->join('cm_nomina','cm_valuacionr.id_siniestro = cm_nomina.id_siniestro');
$this->db->join('cm_empleado','cm_nomina.id_empleado = cm_empleado.id');
$this->db->where('cm_valuacion.id_siniestro',$data);
//$this->db->order_by('id','asc');

return $this->db->get($table);

}

}
?>

//table data output


name mano_obra cH cP cM mecanica tipo_r empleado precio nomina refaccion refaccion1
MyStore CAMBIO DE RADIADOR 0 0 0 250 0 DAVID 1850.00 89.00 REDIADOR REDIADOR

MyStore CAMBIO DE RADIADOR 0 0 0 250 0 DAVID 150.00 89.00 REDIADOR REDIADOR

MyStore CAMBIO DE RADIADOR 0 0 0 250 0 DAVID 1850.00 89.00 REDIADOR RADIADOR

MyStore CAMBIO DE RADIADOR 0 0 0 250 0 DAVID 150.00 89.00 REDIADOR RADIADOR

MyStore CAMBIO DE RADIADOR 0 0 0 250 0 DAVID 1850.00 89.00 REDIADOR ANTICONGELANTE

MyStore CAMBIO DE RADIADOR 0 0 0 250 0 DAVID 150.00 89.00 REDIADOR ANTICONGELANTE

Answer

It seems there is additional data being brought over from you joins causing the duplicates to be formed.

I believe you need to add a GROUP BY at the end of the call

Potentially:

$this->db->group_by('refaccion1');

Basically what are you looking to remove from the dataset? DISTINCT on its own wont really do much in this instance.

Basically, if you omitted the select statement.. there would be different data in some results causing the 'duplicates'. Try running the query directly in SQL query and see the results.