CODEWITHSUNDEEP
mysqldatabasecodeigniter
ethio
ethio

Reputation: 539

How can I query this in CodeIgniter

This is what I want:

SELECT DISTINCT first_name,last_name 
FROM employees e 
INNER JOIN salaries s ON e.emp_no = s.emp_no 
WHERE e.birth_date > '1963-01-01' 
AND s.salary>150000

I've tried this and I get the names I want but also an additional 12 names. Something is wrong. 

    $this->db->distinct();
    $this->db->select('first_name, last_name');
    $this->db->from('employees');
    $this->db->join('salaries s', 'e.emp_no = s.emp_no', 'inner');
    $this->db->where('e.birth_date > 1963-01-01');
    $this->db->where('s.salary > 150000');

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

Upvotes: 2

Views: 161

Answers (3)

WoMo
WoMo

Reputation: 7246

I added a comment, but I'll add it as a suggested answer.You didn't alias employees in $this->db->from('employees'); Add the e after employees like you did for salaries s.

$this->db->distinct();
$this->db->select('first_name, last_name');
$this->db->from('employees e');
$this->db->join('salaries s', 'e.emp_no = s.emp_no', 'inner');
$this->db->where('e.birth_date > 1963-01-01');
$this->db->where('s.salary > 150000');

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

Edit: The date string in the where clause is not quoted. Try updating the statement to quote the string, or pass it in as the second argument.

$this->db->where('e.birth_date > "1963-01-01"');

or

$this->db->where('e.birth_date >', '1963-01-01');

I am assuming this is the correct format mask for your date column in your database.

Upvotes: 2

ethio
ethio

Reputation: 539

In the where clause, the operator should be entered in the first parameter to be compared with the second parameter, the values.

$this->db->where('e.birth_date >', 1963-01-01);
$this->db->where('s.salary >', 150000');

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

it is easy if active record is confusing you. You can simple use query function

$query  =   "SELECT 
                DISTINCT first_name,
                last_name 
            FROM employees e 
            INNER JOIN salaries s ON e.emp_no = s.emp_no 
            WHERE e.birth_date > '1963-01-01' 
            AND s.salary>150000";
$this->db->query($query);

Upvotes: 0

Related Questions