join 3 tables in mysql codeigniter
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');
}
avinashse
Updated on July 13, 2022Comments
-
avinashse almost 2 years
I have 3 tables in my database :-
- tbl_roles(role_id,role_name);
- tbl_users(id,role_id,username,email,password);
- tbl_tickets_replies(id,ticket_id,user_id,role_id,comments)
role_id, id, id
are primary keys of corresponding tables. i need :-- username from tbl_users.
- role_name from tbl_roles.
- comments from tbl_tickets
where
ticket_id
fromtbl_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: 1066Not 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
) JOINtbl_users
ONtbl_users
.id
=tbl_tickets_replies
.user_id
JOINtbl_roles
ONtbl_roles
.role_id
=tbl_tickets_replies
.role_id
WHEREtbl_tickets_replies
.ticket_id
= '6'Filename: C:\wamp\www\local.helpdesk.com\bonfire\codeigniter\database\DB_driver.php
Line Number: 330`
-
Yan Berk almost 12 yearsRemove the
from
clause or remove the table from $this->db->get(). You don't need both. What error do you get? -
Yan Berk almost 12 yearsYou didn't remove
$this->db->from
or did:$this->db->get();
-
avinashse almost 12 yearsthanks yan solved by removing $this->db->from()
-
avinashse almost 12 yearsusing that active records function, queries can be made simple
-
uzsolt almost 12 yearsIf you should build your query dinamically then the raw sql is difficult (or impossible).