php - SQL Joining 2 tables with a consideration of a hidden field/flag -

i have 2 tables in db, im trying join using codeignighter, tables categories_has_products products table can list of products visible.

if visible == 0, not want these products.

the category table irrelevant know category id collect data for, thought id better include the design/table ref.

my attempts

i have attempted myself little rusty sql

$this->db->join('categories_has_products', 'categories_has_products.product_id = products.id', 'left') ->where('visible',1)->get('products');

products

id | name | visible  ---+------+-------- 1  | abc  | 1 2  | def  | 1 3  | ghi  | 0 4  | jkl  | 1 5  | mno  | 1 

categories_has_products

id | category_id | product_id|  ---+--------+ 1  | 1      | 1 2  | 1      | 3 3  | 1      | 4 

categories

id | name   |  ---+--------+ 1  | fruit  |  1  | drinks |  

expected results

id | name | visible  ---+------+--------- 1  | abc  | 1 4  | jkl  | 1 

based on result set want, shouldn't using left join. should work (untested, naturally)

    $this->db->select('products.*');     $this->db->from('products');     $this->db->join('categories_has_products', 'categories_has_products.product_id = products.id', 'inner');     $this->db->where('products.visible !=', 0);