SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias

Question

class Store extends AppModel {
    var $useTable = 'store';
    var $belongsTo = array(
        'Employee' => array(
            'className' => 'Employee',
            'foreignKey' => 'employee_store'
             )
    ); 
    }

Controller

public function index(){ 
$options=array(             
        'joins' =>
                  array(
                    array(
                        'table' => 'Employee',
                        'alias' => 'Employee',
                        'foreignKey' => true,
                        'conditions'=> array('Employee.employee_store = Store.store_name')
                    )

));
$coupons = $this->Store->find('all', $options);
}

I am getting error SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias .

Query is displaying like below

SELECT `Store`.`id`,
       `Store`.`store_name`,
       `Store`.`store_address`,
       `Store`.`store_phone`,
       `Store`.`store_email`,
       `Store`.`store_website`,
       `Store`.`date_enter`,
       `Store`.`store_shortcode`,
       `Employee`.`id`,
       `Employee`.`employee_name`,
       `Employee`.`employee_phone`,
       `Employee`.`employee_email`,
       `Employee`.`employee_username`,
       `Employee`.`employee_password`,
       `Employee`.`employee_store`,
       `Employee`.`date_enter`
FROM `billing`.`store` AS `Store`
LEFT JOIN `billing`.`employee` AS `Employee` ON (`Store`.`employee_id` = `Employee`.`id`)
JOIN `billing`.`Employee` AS `Employee` ON (`Employee`.`employee_store` = `Store`.`store_name`)
WHERE 1 = 1

you use $hasMany instead of $belongsTo $this->Store->find('all'); there is no need to joining query becase hasmany aleready join your query — Sharma Vikram
i need to join store_name with employee_store(store_name=employee_store) . but current its joining store_name with id (store_name=id). — user4030491

Sadikhasan Sadikhasan · Accepted Answer · 2015-02-02T10:49:51

First you have to unbindModel because you are already bind Employee model in Store model that's why it conflict with your Model.

public function index(){ 
  $this->Store->unbindModel(
        array('belongsTo' => array('Employee')), true
    );

$options=array(             
        'joins' =>
                  array(
                    array(
                        'table' => 'Employee',
                        'alias' => 'Employee',
                        'foreignKey' => true,
                        'conditions'=> array('Employee.employee_store = Store.store_name')
                    )    
));
  $coupons = $this->Store->find('all', $options);
}

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias

1 Answers