[eluser]tochie[/eluser]
Hello CI brethren,
Let me give a brief description of what i want to achieve before stating my problem.
I have two mysql tables -
clients and
subscription. There is only one row per client in the
clients table but multiple entries in the
subscription table. That is, each time a client renews subscription, a new row is added in the
subscription table.
Now here is the problem, i want to build a table that shows all the records from the
clients table but pick the subscription expiry date from the
subscription table.
Code:
$this->db->select('*');
$this->db->from('clients');
$this->db->join('subscription','subscription.pc_reg = computers.pc_reg');
//$this->db->where('subscription.pc_date', 'clients.sub_date');
Now this lists all the entries for every client from the subscription table.
My problem is that i want only the latest subscription row for each client instead of listing all rows for the client.
Now, when a new subscription is paid, the expiry date is also updated on the clients table. So i want to try matching the
client_name AND
subscription_date(the updated date from the
clients table, against the most recent date in the
subscription table) in both tables.
I cant even think of how to write the JOIN statement (mysql rookie).
Please anyone suggest where i adjust the codes or if there is a better approach to achieving my goal...
Muchos Gracias!