SQL & PL/SQL

Hi,

I am trying to join three tables in Oracle but getting unexpected results. The below is the situation.

Table A has 10 rows and I want the row with Max date, Table B has 20 rows and I want the row with Max date, so from these 2 tables, I want 2 rows. Similarly there are 10 tables with huge amount of data.
So I created another table called as Key table and extracted Table Name, Table Key (common across all tables and called plan code) and effective date (this is max date)

So Key table has a row from each table now.

When I am joining Table A (E221), Table B (E227) and Key table with below query, it does not result in any row. Can you please suggest on how it should be resolved. The below is the data from 3 tables.

Table A (E221)
PLAN_CODE DATE CAR GRP
12040005 19900801 1204 0005 20
12040005 19850201 1204 0005 19
12040005 19840801 1204 0005 20
12040004 20080806 1204 0004 20
12040004 20080804 1204 0004 20
12040004 20070701 1204 0004 20
12040004 20060101 1204 0004 20
12040004 20020101 1204 0004 20
12040004 20010730 1204 0004 20

TABLE B (E227)

12040005 19850201 1204 0005 0005
12040005 19840801 1204 0005 0080
12040004 20091001 1204 0004 6782
12040004 20070901 1204 0004 6782
12040004 20051101 1204 0004 6782

Key Table

E221 12040004 20080806
E221 12040005 20080806
E227 12040004 20091001
E227 12040005 20091001

Query Used
SELECT E221.*,
E227.* ,
KEY_PLAN_CODE.*
FROM E221
INNER JOIN KEY_PLAN_CODE
ON KEY_PLAN_CODE .EC_PLAN_CD = E221.EC_PLAN_CD AND KEY_PLAN_CODE.MAX_EFF_DATE = E221.cg_cvr_BS_EFF_DT
AND KEY_PLAN_CODE.EC_TRAN_CODE = E221.EC_TRAN_CODE
INNER JOIN E227
ON (KEY_PLAN_CODE.EC_PLAN_CD =E227.EC_PLAN_CD
AND E227.PLAN_EFF_DT=KEY_PLAN_CODE.MAX_EFF_DATE
AND KEY_PLAN_CODE.EC_TRAN_CODE = E227.EC_TRAN_CODE)

Any suggestions would be helpful.