Getting error : Every derived table must have its own alias

Question

I am trying to join two tables based on userid. I have two tables pr_resignation_requests (column name: userids) and pr_users_details (column name: userid). I want to select all entires in both the tables where userid = ".$val.". I am getting this error:

Error Number: 1248

Every derived table must have its own alias

SELECT * FROM pr_resignation_requests LEFT JOIN(SELECT firstname,lastname FROM pr_users_details WHERE userid = 81 )

My controller code is :

function view_exit_details(){
   global $SITE,$USER;
   $data = array();
   $data['row'] = new stdClass();
   $data['row'] = $this->admin_init_elements->set_post_vals($this->input->post());
   $data['error_message'] = '';
   $data['row']->id = $data['id'] = $this->uri->segment(3); 
   
   $data['action'] = 'add';
   $data['heading'] = 'Add';   
   $data['msg_class'] = 'sukses';
   
   
   $data['path']=$path;  
   $post_action = $this->input->post('action');
   if($post_action=='add' || $post_action =='update' ){
    $post_array = $this->input->post();
    $action = ($post_action == 'add')?'inserted':'updated';
    $data['error_message'] = $this->exit_common->add_edit_attendance_issue($post_array,$action);
    
   }
   if($data['id']>0){
     $data['rows']=$this->exit_common->get_cat_array2('pr_resignation_requests','id',$data['id'],'');
     
     $data['action'] = 'update';
    }
   $this->data['maincontent'] = $this->load->view('maincontents/view_resignation_action', $data,true); 
   $this->load->view('layout', $this->data);
   
  }

My Model code is:

  function get_cat_array2($table_name,$col=NULL,$val=NULL,$orderby=NULL){
   
   $result=array();
   $sql = "SELECT * FROM `".$table_name."` LEFT JOIN(SELECT firstname,lastname FROM pr_users_details WHERE userid = `".$val."` )";
   

   $query=$this->db->query($sql); 
   
   if($query->num_rows() > 0){
    $result=$query->result();
   }
   return $result;
 
   
   
  }

Possible duplicate of [every derived table must have its own alias](http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias) — 1000111, Jul 26 '16 at 07:04
@riya . changed query to : $sql = "SELECT * FROM `".$table_name."` LEFT JOIN pr_users_details ON pr_resignation_requests.userids=pr_users_details.userid;"; getting the same error — Shashank Bhat, Jul 26 '16 at 07:20
@ShashankBhat what is error then?? and if u know table_name then used it directly because you used it any how in ON checking — riya, Jul 26 '16 at 07:23
$sql = SELECT * FROM pr_resignation_requests LEFT JOIN ( SELECT firstname,lastname FROM pr_users_details WHERE userid = 81; ) AS a ON (pr_resignation_requests.userids=pr_users_details.userid); but a blank page is what i am getting — Shashank Bhat, Jul 26 '16 at 07:26

score 0 · Answer 1 · edited Jul 26 '16 at 10:07

0

Use the next code snippet:

SELECT *
FROM  pr_resignation_requests
LEFT JOIN 
    (
        SELECT firstname,lastname FROM pr_users_details WHERE userid = 81;
    ) AS a ON  (add u r code of primary key checking)

edited Jul 26 '16 at 10:07

Farside

9,923
4
47
60

answered Jul 26 '16 at 07:18

riya

116
15

$sql = SELECT * FROM pr_resignation_requests LEFT JOIN ( SELECT firstname,lastname FROM pr_users_details WHERE userid = 81; ) AS a ON (pr_resignation_requests.userids=pr_users_details.userid); – Shashank Bhat Jul 26 '16 at 07:25
@ShashankBhat what is error then?? pr_resignation_requests.userids=a.userid – riya Jul 26 '16 at 07:27
$sql = SELECT * FROM pr_resignation_requests LEFT JOIN ( SELECT firstname,lastname FROM pr_users_details WHERE userid = 81; ) AS a ON (pr_resignation_requests.userids=a.userid); still getting blank page – Shashank Bhat Jul 26 '16 at 07:30
@ShashankBhat print u r result first!!!! did u get u r result if yes then no error in query – riya Jul 26 '16 at 07:32
I am not getting results, now i am using this query. $sql = "SELECT * FROM pr_resignation_requests LEFT JOIN ( SELECT firstname,lastname FROM pr_users_details WHERE userid = `".$val."`; ) AS a ON (pr_resignation_requests.userids=a.userid);"; and getting an error 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 '; ) AS a ON (pr_resignation_requests.userids=a.userid)' at line 1 – Shashank Bhat Jul 26 '16 at 08:11
@ShashankBhat removed (pr_resignation_requests.userids=a.userid) bracket and try – riya Jul 26 '16 at 09:45

Getting error : Every derived table must have its own alias

1 Answers1