CODEWITHSUNDEEP
mysql
sweety
sweety

Reputation: 73

Most recent record on left join

I am using mysql, i have table A which has account information and B table which has demographic information such as date of birth, address, and insurance policy information(primary,secondary).Table B's insurance feilds is updated so i want the most recent data from table B.Table B dont have any date feild. So how do i get the most recent record from B which is to be joined with A later.

select  
   A.num,
   A.openingamount,
   A.action,
   A.current_status,
   B.dob,
   B.primary_policy,
   B.secondary_policy 
from A 
left outer join B 
    on (B.uniqueid = A.ponum) 
where A.num ='123'

Upvotes: 1

Views: 264

Answers (3)

Sam Wilson
Sam Wilson

Reputation: 4512

juergen d is correct in eir comment above, stating that you need some ordered column in table B to determine what is the most recent. If B.uniqueid is an auto-incrementing column, then you can use that; if it isn't then you'll need to add a new column by which to order B.

Then, you can get the data with a double-join to B, where the second join is used to find B rows that are greater than the max uniqueid (i.e. to find nothing).

SELECT
  A.num, A.openingamount, A.action, A.current_status,
  B.dob, B.primary_policy, B.secondary_policy 
FROM A 
  LEFT JOIN B ON (B.a_ponum = A.ponum) 
  LEFT JOIN B2 ON (B2.a_ponum = A.ponum AND B2.uniqueid > B.uniqueid) 
WHERE
  B2.uniqueid IS NULL
  AND A.num ='123'

Upvotes: 1

Devart
Devart

Reputation: 121922

You also may want to add an auto-updated TIMESTAMP field -

ALTER TABLE B
  ADD COLUMN ts
    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Automatic Initialization and Updating for TIMESTAMP.

Then use this field to find the most recent record.

Upvotes: 0

Boby
Boby

Reputation: 822

If you don't have a date column, you need an increment column:

select  
   A.num,
   A.openingamount,
   A.action,
   A.current_status,
   B.dob,
   B.primary_policy,
   B.secondary_policy 
from A 
left outer join B 
    on (B.uniqueid = A.ponum) 
where A.num ='123'
order by B.increment_id desc

Upvotes: 0

Related Questions