CI Database/Active Records failing for what I need to do?

[eluser]Unknown[/eluser]
Hello.

I've never used JOIN queries before today. After reading about them, I can see the IMMENSE amount of power they offer and I can see how using them would make my life a hell of a lot easier.

With that said, I was trying to go about implementing the usage of JOIN queries into this application I'm writing. I wrote out this query:

SELECT c_transactions.c_id, c_transactions.cash, c_transactions.debt, c_transactions.product, customers.alias FROM c_transactions, customers WHERE c_transactions.date > "' . $yesterday . '" AND (c_transactions.c_id = customers.id)

When I replace $yesterday with '2010-11-01 00:00:00' in Emma (my MySQL administration tool) the query runs fine and returns exactly the dataset that I want.

I tried re-writing the query using CI's active records class. The code is as follows. I couldn't get this method to work at all:

Code:

$this->db->select('c_transactions.date, c_transactions.c_id, c_transactions.cash, c_transactions.debt, c_transactions.product, customers.alias');
        $this->db->from('c_transactions, customers');
        $this->db->where('date >', $yesterday);
        $this->db->where('c_transactions.c_id', 'customers.id');
        $transactions = $this->db->get();

When I saw this didn't work, I decide to try directly setting the SQL, using the code:

Code:

$this->db->sql('SELECT c_transactions.c_id, c_transactions.cash, c_transactions.debt, c_transactions.product, customers.alias FROM c_transactions, customers WHERE c_transactions.date > "' . $yesterday . '" AND (c_transactions.c_id = customers.id);');
        $transactions = $this->db->query();

This also doesn't work. It returns no errors or anything. I just get a blank screen. That prompted me to enter echo() statements after every line of code, and I can see it's breaking right when I call $this->db->sql().

Any ideas? Maybe I'm simply utilizing the Active Records class improperly. I would prefer to use Active Records as opposed to directly setting the SQL, but I'd be happiest just to find a solution at this point.

Thanks!