CODEWITHSUNDEEP
phpmysqlcodeigniterjoinwhere-clause
user1549397
user1549397

Reputation: 651

in codeigniter how do i do a join with a where clause

so i have two tables, and i want to get all the rows from table 1 that meet the where clause conditions, and then join them with table two based on the join conditions.

here are the sample tables:

table1:

col1   col2  col3
1      a     val1
2      b     val2
3      c     val3

table2:

col1   col3
1      someval1
2      someval2
3      someval3

now i want to grab all the rows in table 1 where col1 = 2, and join those rows with rows from table2 where table2.col1 = table1.col1. Does that make sense?

Upvotes: 7

Views: 61686

Answers (3)

Jah Yusuff
Jah Yusuff

Reputation: 739

$this->db->select('book_id, book_name, author_name, category_name');
$this->db->from('books');
$this->db->join('category', 'category.category_id = books.category_id');
$this->db->where('category_name', 'Self Development');
$query = $this->db->get();

// Produces SQL:
 select book_id, book_name, author_name, category_name from books 
 join category on category.category_id = books.category_id 
 where category_name = "Self Development"

Upvotes: 0

Chris Trahey
Chris Trahey

Reputation: 18290

It's been a while since I wrote CI, but as per this docs page, your solution might look like this: 

$this->db->select('*');
$this->db->from('table1');
$this->db->join('table2', 'table1.col1 = table2.col1');
$this->db->where('table1.col1', 2);

$query = $this->db->get();

note this answer is in no way to be construed as an endorsement of working with Code Igniter ;-)

Upvotes: 18

Yagi
Yagi

Reputation: 511

Try this :

$this->db->select('*'); // Select field
$this->db->from('table1'); // from Table1
$this->db->join('table2','table1.col1 = table2.col1','INNER'); // Join table1 with table2 based on the foreign key
$this->db->where('table1.col1',2); // Set Filter
$res = $this->db->get();

Hope it helps :)

Upvotes: 3

Related Questions