CODEWITHSUNDEEP
mysqlcodeigniteractiverecord
Anupal
Anupal

Reputation: 1522

How do we Multiple JOIN in Active recordsCodeigniter

Hello guys can you please help me in as am beginner for Database and Codeigniter. How do we write this multiple inner join and left join query in active records.

SELECT suppliers.*, category.strCategory, category_1.strCategory AS strParent, 
tblcitylist.city_name
FROM ((suppliers INNER JOIN category ON suppliers.intCat=Category.intId) INNER JOIN tblcitylist ON 
suppliers.intCity=tblcitylist.city_id) LEFT JOIN category AS category_1 ON 
category.intParent=Category_1.intId
WHERE status='y';

I have three tables suppliers, tblcitylist and category. want to fetch data for supplier having intCat and intCity resulting with data of suppliers table with city name(city_name) and category name (strCategory). Thanks

Upvotes: 1

Views: 153

Answers (2)

Warren.S
Warren.S

Reputation: 162

Perhaps this is what you're after...

$query = $this->db->select('suppliers.*, category.strCategory, category_1.strCategory AS strParent, tblcitylist.city_name')
            ->from('suppliers')
            ->join('category',                  'suppliers.intCat=Category.intId',          'inner')
            ->join('tblcitylist',               'suppliers.intCity=tblcitylist.city_id',    'inner')
            ->join('category as `category_1',   'category.intParent=Category_1.intId',      'left')
            ->where('status','y')
                ->get();

CI DB Docs: https://www.codeigniter.com/user_guide/database/index.html

Upvotes: 1

Glyph
Glyph

Reputation: 555

This seems like a case where using straight SQL would be simpler. Not to mention it executes faster.

$sql = "SELECT........";
$query = $this->db->query($sql);

Upvotes: 0

Related Questions