MAX value in subsidiary table question

[jcf27]

I have the following two tables --

Table A

Apkid, name, transaction (distinct records)

Table B

Bpkid, Bdate, Bvalue

Table B gets a new record every day. For example

1,10/01/02, 3
1,10/02/02, 5
1,10/03/03, 7

Apkid and Bpkid are the same.

The objective is to create a select statement where I have all the fields from Table A (no problem here) and the value of the latest date found in Table B (7 in this case). I tried using the MAX value in the join at no avail. It returns all rows from Table B.

By the way, I'm using Oracle.

Many thanks in advance!

J.

 

[rac2]

Maybe -

SELECT TableA.*, TableB.dailyDate, TableB.valueForTheDay
FROM TableA
JOIN (
       SELECT Bpkid, MAX(dailyDate) AS last_day
       FROM TableB
       GROUP BY Bpkid
      ) B2
  ON TableA.Apkid = B2.Bpkid
JOIN TableB ON B2.last_day = TableB.dailyDate
           AND B2.Bpkid = TableB.Bpkid

 

[angiole]

Please provide your query that failed... AA 8~)