错误号：1066不唯一的表/别名：codeigniter中的'service'

Question

Want to get all services in one to many relationship My code is

$this->db->select('*');
    $this->db->from('service');
    $this->db->join('user', 'user.user_email = service.user_email', 'inner');
    $this->db->join('service', 'service.user_email = user.user_email', 'inner');
    $query = $this->db->get();

But it gives me an error

Error Number: 1066

Not unique table/alias: 'service'

SELECT * FROM (`service`) INNER JOIN `user` ON `user`.`user_email` = `service`.`user_email` INNER JOIN `service` ON `service`.`user_email` = `user`.`user_email`

Filename: C:\xampp\htdocs\service\system\database\DB_driver.php

If I do without

$this->db->from('service');

Then it gives syntax error

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN `user` ON `user`.`user_email` = `service`.`user_email` INNER JOIN `se' at line 2

SELECT * INNER JOIN `user` ON `user`.`user_email` = `service`.`user_email` INNER JOIN `service` ON `service`.`user_email` = `user`.`user_email`

Filename: C:\xampp\htdocs\service\system\database\DB_driver.php

Answer 1

You are trying to join the service table to the user table and then trying to join the service table again.

This isn't something you should generally be trying to do, and when you do it this way, the rendered SQL statement contains two attempts to reference the service table. It's the second JOIN that causes the database pain and it throws the error that you're seeing.

In this instance, just use:

$this->db->select('*');
$this->db->from('service');
$this->db->join('user', 'user.user_email = service.user_email', 'inner');
$query = $this->db->get();

Since the

$this->db->join('service', 'service.user_email = user.user_email', 'inner');

is redundant here (you've already joined these tables on that field.