CODEIGNITER + MySQL DB
Hi I have two tables
Table 1
<pre><code>
CREATE TABLE `users` (
`user_id` int(255) NOT NULL,
`user_name` varchar(100) NOT NULL,
`user_email` varchar(100) NOT NULL,
`user_password` varchar(200) NOT NULL,
`user_phone` varchar(20) NOT NULL,
`building_units` int(200) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status` enum('0','1') NOT NULL DEFAULT '1' COMMENT '1 = Active(default), 0 = Inactive',
`user_role` enum('0','1','2','3') NOT NULL DEFAULT '2' COMMENT '0=Manager,1=Admin,2=User,3=Tenant',
`user_ip` int(39) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Table 2
<pre><code>
CREATE TABLE `building_admins_tbl` (
`id` int(255) NOT NULL,
`building_id` int(255) NOT NULL COMMENT 'FK ',
`building_admin_id` int(255) DEFAULT NULL COMMENT 'FK',
`user_id` int(255) DEFAULT NULL,
`tenant_id` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And I have written the following query.
<pre><code>
$select = 'users.user_id,users.user_name,users.user_role,users.status,building_admins_tbl.building_admin_id';
$join_str = 'users.user_id != building_admins_tbl.building_admin_id';
$where = ['users.user_role'=>'1','users.status'=>'1'];
$q =$this->db
->select($select)
->where($where)
->from('users')
->join('building_admins_tbl',$join_str)
->get();
$admin_list =$q->result_array();
In the above query, Join string is important, I want to get only those rows where(users.user_id != building_admins_tbl.building_admin_id) with where condition as well.
But I am not getting the expecting result, Please help me whats wrong in the query. Help appreciated.
Thanks
