learningbyexample learningbyexample - 6 months ago 28
SQL Question

How do I make aliases on codeigniter when using the join library

I have tried to make aliases into the query so that it does not give the error of not unique tables.

as you can see i've placed 'AS' with a new name after but it gives me an error. How am I doing this wrong?

this is what I have in my model:

<?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->select('
cm_valuacion.tipo_r AS valR,
cm_valuacion.mano_obra AS valM,
cm_valuacion.hojalateria AS valH,
cm_valuacion.pintura AS valP,
cm_valuacion.mecanica AS valMec,
cm_valuacion.refaccion AS valRef,
cm_valuacion.shojalateria AS valSH,
cm_valuacion.tipo AS valT,
cm_valuacion.spintura AS valSP,
cm_valuacion.costoHojalateria AS valCH,
cm_valuacion.smecanica AS valSM,
cm_valuacion.costoPintura AS valCP,
cm_valuacion.costoMecanica AS valCM,
cm_valuacion.pv_hojalateria AS valPH,
cm_valuacion.pv_pintura AS valPP,
cm_valuacion.pv_mecanica AS valPM,
cm_valuacion.pc_hojalateria AS valPH,
cm_valuacion.pc_pintura AS valPPin,
cm_valuacion.pc_mecanica AS valPMec,
cm_compras.precio,
cm_proveedor.nombre,
cm_nomina.nomina,
cm_empleado.nombre,
cm_nomina.id_nomina_gral
',FALSE);

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


}
?>


the error that I get is:

A Database Error Occurred
Error Number: 1066
Not unique table/alias: 'cm_valuacion'
SELECT DISTINCT cm_valuacion.tipo_r AS valR, cm_valuacion.mano_obra AS valM, cm_valuacion.hojalateria AS valH, cm_valuacion.pintura AS valP, cm_valuacion.mecanica AS valMec, cm_valuacion.refaccion AS valRef, cm_valuacion.shojalateria AS valSH, cm_valuacion.tipo AS valT, cm_valuacion.spintura AS valSP, cm_valuacion.costoHojalateria AS valCH, cm_valuacion.smecanica AS valSM, cm_valuacion.costoPintura AS valCP, cm_valuacion.costoMecanica AS valCM, cm_valuacion.pv_hojalateria AS valPH, cm_valuacion.pv_pintura AS valPP, cm_valuacion.pv_mecanica AS valPM, cm_valuacion.pc_hojalateria AS valPH, cm_valuacion.pc_pintura AS valPPin, cm_valuacion.pc_mecanica AS valPMec, cm_compras.precio, cm_proveedor.nombre, cm_nomina.nomina, cm_empleado.nombre, cm_nomina.id_nomina_gral FROM (
cm_valuacion
,
cm_proveedor
,
cm_compras
,
cm_valuacion
,
cm_valuacionr
,
cm_nomina
,
cm_empleado
) JOIN
cm_valuacionr
ON
cm_valuacionr
.
id_siniestro
=
cm_valuacion
.
id_siniestro
JOIN
cm_compras
ON
cm_valuacionr
.
id_siniestro
=
cm_compras
.
id_siniestro
JOIN
cm_proveedor
ON
cm_proveedor
.
id
=
cm_compras
.
id_proveedor
JOIN
cm_nomina
ON
cm_nomina
.
id_siniestro
=
cm_compras
.
id_siniestro
JOIN
cm_empleado
ON
cm_nomina
.
id_empleado
=
cm_empleado
.
id
JOIN
cm_nomina_gral
ON
cm_nomina
.
id_nomina_gral
=
cm_nomina_gral
.
id
WHERE
cm_valuacion
.
id_siniestro
= '6489'
Filename: C:\xampp\htdocs\index\system\database\DB_driver.php
Line Number: 330

Answer

The error is in your JOIN clause. When you're joining the same table multiple times you have to assign an alias to the table name. When a table is joined multiple times, SQL doesn't know which table to refer to.

Example:

FROM (cm_valuacion, cm_proveedor, cm_compras, cm_valuacion, cm_valuacionr, cm_nomina, cm_empleado) JOIN cm_valuacionr valnr ON valnr.id_siniestro = cm_valuacion.id_siniestro

Comments