Mysql Join

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('class', 'class.id = tutorial.id');
$query = $this->db->get();
$result = $query->result();


###################### OR ########################


$qry = "SELECT * FROM ittutorial JOIN class ON class.id = tutorial.id";
$query = $this->db->query($qry);
$result = $query->result();
return $result;

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.typee');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid');
$query=$this->db->get();
$data=$query->result_array();

SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`type` FROM (`tbl_user`)
JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.typee');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid');
$this->db->where('tbl_usercategory','admin');
$query=$this->db->get();
$data= $query->result_array();

SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`type` FROM (`tbl_user`)
JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.type');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid','inner');
$query=$this->db->get();

SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`typee` FROM (`tbl_user`)
INNER JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`

$this->db->select('username,amount, configdescription');
$this->db->from('tbl_user');
$this->db->join('tbl_usersubscription','tbl_usersubscription.userid=tbl_user.userid','inner');
$this->db->join('tbl_subscription',
'tbl_usersubscription.subscription_config_id=tbl_subscription.configid','inner');

$sql="select np.*,nwip.i_techcommtt_id, nwip.added_on, nwip.added_by, nwip.i_dept_id, td.Abbreviations, td.ID, tc.CommitteeNumber from nwip_techcommtt_allocation nwip
inner join proposals np on np.pki_id=nwip.i_nwip_id
inner join Tech_Comm tc on tc.ID=nwip.i_techcommtt_id
inner join Tech_Dep td on td.ID=tc.DepartmentID
where nwip.i_techcommtt_id in(".$this->db->escape_str($commttid).") AND nwip.i_status=1 AND np.status='1';";

$query = $this->db->query($sql);
$rows = $query->result_array();
return $rows;

SELECT `username`, `amount`, `configdescription` FROM (`tbl_user`)
INNER JOIN `tbl_usersubscription` ON `tbl_usersubscription`.`userid`=`tbl_user`.`userid`
INNER JOIN `tbl_subscription` ON `tbl_usersubscription`.`subscription_config_id`=`tbl_subscription`.`configid`

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.type');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid','Left');
$query=$this->db->get();

SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`typee` FROM (`tbl_user`)
Left JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.type');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid','Right');
$query=$this->db->get();

SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`typee` FROM (`tbl_user`)
Right JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`

$this->db->select('*');
$this->db->from('table1');
$this->db->join('table2', 'table1.id = table2.id');
$this->db->join('table3', 'table1.id = table3.id');
$query = $this->db->get();

$this->db->select('*');
$this->db->from('users');
$this->db->join('profile_image', 'profile_image.user_id = users.id');
$this->db->join('city', 'city.user_id = users.id','left');
$this->db->join('post', 'post.user_id = users.id','left');
$this->db->join('friends', 'friends.user_id = users.id','left');
$this->db->where('users.id', $id);
$query = $this->db->get();

// Result
Select *from users
join profile_image on profile_image.user_id = users.id
left join city on city.user_id = users.id
left join post on post.user_id = users.id
left join friends on friends.user_id = users.id
where users.id = $id

public function get_search_key($key)
{
$this->db->select("*");
$this->db->from('student_details');
$this->db->like('rollno', $key);
$this->db->or_like('first_name', $key);
$this->db->or_like('last_name', $key);
$this->db->or_like('class', $key);
$this->db->or_like('section', $key);

$this->db->or_like('parents_details.father_firstname',$key);
$this->db->or_like('parents_details.father_lastname',$key);

$this->db->join('parents_details','parents_details.sid = student_details.sid');

//$this->db->order_by('sid', 'DESC');
$query = $this->db->get();
return $query->result();

}

public function getStudentInfo($studentid){
$query = $this->db->select("st.id, st.name, st.class, mk.maths, mk.science")
->from("students as st")
->join("marks as mk", "mk.student_id = st.id", "inner")
->where("st.id", $studentId)
->get();
return $query->result();
}

this->db->select('*');
$this->db->from('users');
$this->db->join('comments','comments.user_id = users.u_id');
$this->db->get();

$this->db->select('*')
->from('users')
->join('comments','comments.user_id = users.u_id')
->join('city','city.user_id = users.u_id')
->get();

$this->db->select('*')
->from('users')
->where('users.u_id',1)
->join('comments','comments.user_id = users.u_id')
->join('city','city.user_id = users.u_id')
->get();

$this->db->query("select table1.ud_id,table1.ud_date,table1.up_lat_profit_date,table1.ud_currency
,sum(table1.ud_amount*table3.dp_percentage/100) as profit
from table1
JOIN `table2` ON `table2`.`deposit_id` = `table1`.`ud_id`
JOIN `table3` ON `table3`.`dp_id` = `table2`.`daily_profit_id`
where `table1`.`ud_status` = 1
AND `table3`.`dp_status` = 1
AND `table1`.`user_plan_id` = $up_id
AND table2.pr_status = 1
group by table1.ud_id");

$this->db->select('Table1.*, Table2.*');
$this->db->from('Table1');
$this->db->join('Table2', 'Table2.id = Table1.id');
$query = $this->db->get();

//Produces:

SELECT `Table1`.*, `Table2`.* FROM `Table1`
JOIN `Table2` ON `Table2`.`id` = `Table1`.`id`

$this->db->select('Table1.*, Table2.*');
$this->db->from('Table1');
$this->db->join('Table2', 'Table2.id = Table1.id','inner');
$query = $this->db->get();

//Produces:

SELECT `Table1`.*, `Table2`.* FROM `Table1`
INNER JOIN `Table2` ON `Table2`.`id` = `Table1`.`id`

$this->db->select('Table1.*, Table2.*');
$this->db->from('Table1');
$this->db->join('Table2', 'Table2.id = Table1.id');
$this->db->where('Table1.field_name','field_value');
$query = $this->db->get();

//Produces:

SELECT `Table1`.*, `Table2`.* FROM `Table1`
JOIN `Table2` ON `Table2`.`id` = `Table1`.`id`
WHERE `Table1`.`field_name` = ‘field_value’

$this->db->select('u.id, u.username, c.name');
$this->db->from('user as u');
$this->db->join('companies as c', 'u.company_id = c.id');
$this->db->where('LOWER(u.username)=', strtolower('foobar'));
$query = $this->db->get();

Send second parameter FALSE to not escape special characters.

$this->db->select('u.id, u.username, c.name', false);

$this->db->select('u.id,u.name,pi.image,p.post,f.id as fid');
$this->db->from('users as u');
$this->db->join('profile_image as pi ', 'pi.user_id = u.id');
$this->db->join('city as c', 'c.user_id = u.id','left');
$this->db->join('post as p ', 'p.user_id = u.id','left');
$this->db->join('friends as f ', 'f.user_id = u.id','left');
$this->db->where('u.id', $id);
$query = $this->db->get();


The above example will produce output something like this-
// Select u.id,u.name,pi.image,p.post,f.id as fid from users
join profile_image as pi on profile_image.user_id = users.id
left join city as c on c.user_id = u.id
left join post as p on p.user_id = u.id
left join friends as f on f.user_id = u.id
where u.id = $id

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.typee');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid');
$query=$this->db->get();
$data=$query->result_array();

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.typee');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid');
$this->db->where('tbl_usercategory','admin');
$query=$this->db->get();
$data= $query->result_array();

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('class', 'class.id = tutorial.id');
$this->db->where(array('class.id' => 786));
$query = $this->db->get();
$result = $query->result();


###################### OR ########################


$qry = "SELECT * FROM ittutorial
JOIN class ON class.id = tutorial.id
where class.id = 786";
$query = $this->db->query($qry);
$result = $query->result();
return $result;

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.type');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid','inner');
$query=$this->db->get();


SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`typee`
FROM (`tbl_user`)
INNER JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`



Used Three table Inner Join in codeigniter

$this->db->select('username,amount, configdescription');
$this->db->from('tbl_user');
$this->db->join('tbl_usersubscription','tbl_usersubscription.userid=tbl_user.userid','inner');
$this->db->join('tbl_subscription',
'tbl_usersubscription.subscription_config_id=tbl_subscription.configid','inner');


SELECT `username`, `amount`, `configdescription`
FROM (`tbl_user`)
INNER JOIN `tbl_usersubscription` ON `tbl_usersubscription`.`userid`=`tbl_user`.`userid`
INNER JOIN `tbl_subscription` ON `tbl_usersubscription`.`subscription_config_id`=`tbl_subscription`.`configid`

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.type');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid','Left');
$query=$this->db->get();



SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`typee`
FROM (`tbl_user`)
Left JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`

$this->db->select('tbl_user.username,tbl_user.userid,tbl_usercategory.type');
$this->db->from('tbl_user');
$this->db->join('tbl_usercategory','tbl_usercategory.usercategoryid=tbl_user.usercategoryid','Right');
$query=$this->db->get();



SELECT `tbl_user`.`username`, `tbl_user`.`userid`, `tbl_usercategory`.`typee`
FROM (`tbl_user`)
Right JOIN `tbl_usercategory` ON `tbl_usercategory`.`usercategoryid`=`tbl_user`.`usercategoryid`

/*
$sql="select np.*,nwip_alloc.i_techcommtt_id, nwip_alloc.added_on, nwip_alloc.added_by, nwip_alloc.i_dept_id, td.Abbreviations, td.ID, tc.CommitteeNumber from tech_commtt_allocation nwip_alloc
inner join propos np on np.pki_id=nwip_alloc.i_nwip_id
inner join TechComm tc on tc.ID=nwip_alloc.techcommttid
inner join TechDep td on td.ID=tc.DepID
where nwip_alloc.i_techcommtt_id in (".$this->db->escape_str($commttid).") AND nwip_alloc.i_status=1;";

$query = $this->db->query($sql);
$rows = $query->result_array();
return $rows;
*/




######################### OR ############################



$this->db->select('np.*,nwip_alloc.i_techcommtt_id, nwip_alloc.added_on, nwip_alloc.added_by, nwip_alloc.i_dept_id, td.Abbreviations, td.ID, tc.CommitteeNumber');
$this->db->from('techcomt as nwip_alloc');
$this->db->join('propos as np', 'np.pki_id=nwip_alloc.i_nwip_id','inner');
$this->db->join('Techcomm as tc', 'tc.ID=nwip_alloc.techcommid','inner');
$this->db->join('TechDep as td', 'td.ID=tc.DepID','inner' );

$this->db->where_in('nwip_alloc.i_techcommtt_id',$commttid,false);
$this->db->where('nwip_alloc.i_status', '1');
$query = $this->db->get();
//echo $this->db->last_query();

$rows = $query->result_array();
return $rows;

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('class', 'class.id = tutorial.id');
$this->db->join('comments', 'comments.id = tutorial.id');
// $this->db->where(array('class.id' => 786));
$query = $this->db->get();
$result = $query->result();


###################### OR ########################


$qry = "SELECT * FROM ittutorial
JOIN class ON class.id = tutorial.id
JOIN comments ON comments.id = tutorial.id
// where class.id = 786";
$query = $this->db->query($qry);
$result = $query->result();
return $result;

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('class', 'class.id = tutorial.id','left');
$this->db->join('comments', 'comments.id = tutorial.id','left');
$query = $this->db->get();
$result = $query->result();


###################### OR ########################


$qry = "SELECT * FROM ittutorial
LEFT JOIN class ON class.id = tutorial.id
LEFT JOIN comments ON comments.id = tutorial.id
$query = $this->db->query($qry);
$result = $query->result();
return $result;

$this->db->select('*');
$this->db->from('ittutorial');
$this->db->join('class', 'class.id = tutorial.id','RIGHT');
$this->db->join('comments', 'comments.id = tutorial.id','RIGHT');
$this->db->ORDER_BY('comments.id', ASC);
$query = $this->db->get();
$result = $query->result();


###################### OR ########################


$qry = "SELECT * FROM ittutorial
RIGHT JOIN class ON class.id = tutorial.id
RIGHT JOIN comments ON comments.id = tutorial.id
$query = $this->db->query($qry);
$result = $query->result();
return $result;

$this->db->select('cd.*, edu.*, gt.*, cd.i_status as can_status');
$this->db->from('candetails as cd');
$this->db->join('edudetails as edu','edu.fk_id=cd.pki_id','left');
$this->db->join('gate as gt','gt.fk_id=cd.pki_id','left');
$this->db->where($where);
$this->db->group_by('edu.fk_id');
//$this->db->where('cd.i_status','3');
//$this->db->where('cd.category', $category);
// $this->db->where('cd.discipline', $discipline);
$query=$this->db->get();
// echo $this->db->last_query();
//$response = $query->result_array();
return $result = $query->result();