codeigniter active record or general mysql help

I am query a database to get all the employers names out of my database, but I only want to get the ones whe开发者_开发问答re their ID is present in my jobs table, here is what I am trying to do.

$this->db->select('*')
    ->from('employers')
->join('jobwall', 'jobwall.employers_employer_id = employers.employer_id', 'left');

However this does not return the correct results, how can I select all my employers from the employers table but only if they have data in the jobwall table?

---

You need to add a WHERE clause:

$sql = '
    SELECT *
    FROM employers
    LEFT JOIN jobwall ON jobwall.employers_employer_id = employers.employer_id
    WHERE employers.employer_id
    IN (SELECT employers_employer_id FROM jobwall)
';

$this->db->query($sql);

I'm not sure how complicated this would be to create using Codeigniter's activerecord class.

---

I think a better WHERE clause might be:

SELECT *
FROM employers
LEFT JOIN jobwall ON jobwall.employers_employer_id = employers.employer_id
WHERE jobwall.id IS NOT NULL

This will exclude any rows that don't have a corresponding job.

You should use whatever Primary Key your jobwall table has if jobwall.id doesn't exist.

This can also be written using ActiveRecord easily.

---

I don't understand you exactly, but I think you should use join without left:

this->db->select('*')->from('employers')->join('jobwall', 'jobwall.employers_employer_id = employers.employer_id');