How can I optimize this raw SQL and perhaps implement it via CodeIgniter?

Question

It's been a while since I've written raw SQL, I was hoping someone could help me out in optimizing this SQL query so that it works across, both, MySQL and PostgreSQL.

I would also have to implement this via CodeIgniter (2.x) using ActiveRecord, any help/advice?

       SELECT *
     FROM notaries, contact_notaries
    WHERE notaries.id = contact_notaries.notary_id
AND WHERE (   contact_notaries.city LIKE %$criteria%
           OR contact_notaries.state LIKE %$criteria
           OR contact_notaries.address LIKE %$criteria%)

Thanks!

Answer 1

1. Each query can have just one WHERE clause (you don't need the second)
2. It's much better to put join condition into JOIN rather then WHERE.
3. Are you sure you really need all the columns from 2 tables (*)?
   So I'd refactor it to

SELECT [field_list]
FROM notaries
INNER JOIN contact_notaries ON (notaries.id = contact_notaries.notary_id)
WHERE ( contact_notaries.city LIKE '%$criteria%'
OR contact_notaries.state LIKE '%$criteria'
OR contact_notaries.address LIKE '%$criteria%')

Answer 2

Using a1ex07's query:

SELECT [field_list]
FROM notaries
INNER JOIN contact_notaries ON (notaries.id = contact_notaries.notary_id)
WHERE ( contact_notaries.city LIKE '%$criteria%'
OR contact_notaries.state LIKE '%$criteria'
OR contact_notaries.address LIKE '%$criteria%')

Active record:

$this->db->select(); // Leave empty to select all fields
$this->db->join('contact_notaries', 'notaries.id = contact_notaries.notary_id', 'inner');
$this->db->like('contact_notaries.city', 'criteria');
$this->db->like('contact_notaries.state', 'criteria');
$this->db->like('contact_notaries.address', 'match');
$results = $this->db->get('notaries');

To specify a list of fields you can do $this->db->select('field_1, field_2, ...');.

http://codeigniter.com/user_guide/database/active_record.html