join 3 tables in mysql codeigniter

问题:

I have 3 tables in my database :-

1. tbl_roles(role_id,role_name);
2. tbl_users(id,role_id,username,email,password);
3. tbl_tickets_replies(id,ticket_id,user_id,role_id,comments)

role_id, id, id are primary keys of corresponding tables. i need :-

1. username from tbl_users.
2. role_name from tbl_roles.
3. comments from tbl_tickets

where ticket_id from tbl_tickets_replies = $ticket_id coming as a parameter.

My Model Function is :-

function fetch_comments($ticket_id){
        $this->db->select('tbl_tickets_replies.comments,tbl_users.username,tbl_roles.role_name');
        $this->db->where('tbl_tickets_replies.ticket_id',$ticket_id);
        $this->db->from('tbl_tickets_replies');
        $this->db->join('tbl_users','tbl_users.id = tbl_tickets_replies.user_id');
        $this->db->join('tbl_roles','tbl_roles.role_id=tbl_tickets_replies.role_id');
        $comments = $this->db->get('tbl_tickets_replies');
        return $comments;
     }

this is showing database error i.e., I am doing wrong query. I want to ask how can I join three tables to fetch data from 3 different tables

This error is showing :-

A Database Error Occurred
Error Number: 1066

Not unique table/alias: 'tbl_tickets_replies'

SELECT tbl_tickets_replies.comments, tbl_users.username, tbl_roles.role_name FROM (tbl_tickets_replies, tbl_tickets_replies) JOIN tbl_users ON tbl_users.id = tbl_tickets_replies.user_id JOIN tbl_roles ON tbl_roles.role_id=tbl_tickets_replies.role_id WHERE tbl_tickets_replies.ticket_id = '6'

Filename: C:\wamp\www\local.helpdesk.com\bonfire\codeigniter\database\DB_driver.php

Line Number: 330`

回答1:

You are referring to tbl_tickets_replies twice. Try this:

function fetch_comments($ticket_id){
    $this->db->select('tbl_tickets_replies.comments, 
           tbl_users.username,tbl_roles.role_name');
    $this->db->where('tbl_tickets_replies.ticket_id',$ticket_id);
    $this->db->join('tbl_users','tbl_users.id = tbl_tickets_replies.user_id');
    $this->db->join('tbl_roles','tbl_roles.role_id=tbl_tickets_replies.role_id');
    return $this->db->get('tbl_tickets_replies');
}

回答2:

For complex queries, i prefer using the plain SQL as follows.

$sql = "SELECT....";
$q = $this->db->query($sql);

Btw, try removing the table name from db->get function

$comments = $this->db->get(); //change this

回答3:

Join with condition.

$this->db->select('*'); $this->db->from('articles');
$this->db->join('category', 'category.id = articles.id');
$this->db->where(array('category.id' => 10)); $query =
$this->db->get();