Using mysql JOIN statements

[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!

[eluser]Mike Ryan[/eluser]
Hi,

Can you post the field names for the clients and subscription tables?

Slightly off-topic: Why are you storing the expiry date in both the clients AND the subscription tables? It should only be stored once, and JOINed where necessary.

[eluser]tochie[/eluser]
Hello Mike,

Thanks for your time. These are the field names for the client and subscription tables.

CLIENT: c_id, pc_reg, c_name, c_phone, c_date,
SUBSCRIPTION: s_id, pc_reg, s_amt, s_expire, s_date

I need to JOIN in a way that i get only the most recent payment(form subscription table) by a client(from client table) when displaying his record, instead of displaying all his payments. That was why i update the c_date field in the client table whenever new subscription has been paid.

I'd like to know your approach to solving this.

Thanks

[eluser]Mike Ryan[/eluser]
Hi Tochie,

No problem!

Code:

SELECT * from client as t1
INNER JOIN subscription as t2
  ON t1.c_id = t2.s_id
  AND t1.c_date = t2.s_date
WHERE t1.c_id = $customer_id

This should do it, but I haven't tested it. You could also get rid of c_date and do:

Code:

SELECT * from client as t1
INNER JOIN subscription as t2
  ON t1.c_id = t2.s_id
WHERE t1.c_id = $customer_id
HAVING MAX(t2.s_date)

Both are untested - let me know if they don't work!