CODEWITHSUNDEEP
phpmysqlcodeigniteractiverecord
It worked yesterday.
It worked yesterday.

Reputation: 4617

Joining three tables and filtering data with CodeIgniter ActiveRecord

I have the following three tables 

+----------+ +--------+ +-------------+
| adv      | | member | | removed_adv |
+==========+ +========+ +=============+
| id       | | id     | | id          |
| group    | | group  | | adv_id      |
| category | | email  | | member_id   |
| title    | | pw     | +-------------+
| path     | +--------+
| duration |
+----------+

What I am trying to do is to get all the data from table adv, where the member table's group is equal to the adv table's group. If the member table's id is equal to the _removed_adv_ table's _member_id_, that data should be removed from the result set.

I am using CodeIgniter's Active Record.

Could someone please help me? I really don't know how to do this.

Upvotes: 1

Views: 858

Answers (1)

DevT
DevT

Reputation: 4933

You can try this:

select * from adv join member 
on adv.group=member.group
where member.id not in (select member_id from removed_adv)

For CodeIgniter:

$removed = // get removed records member_id to here;

$CI->db->select('ad');
$CI->db->from('adv ad');
$CI->db->join('mem m', 'ad.group=mem.group', 'left');
$CI->db->where_not_in('mem.id', $removed);
$query = $CI->db->get();

Upvotes: 1

Related Questions