outer join in view

[chrisr]

please help!!! In concept, I think I need an outer join with a table to create this view, because there may or may not be records in this one table and if there are no records in this one table, no data is being pulled for the entire view. i used an outer join for 2 other 'and' statements with this table and it works fine if i have 0 or 1 record, however, i also need to only select the max id record (it is possible to have multiple records) from this table and this is causing the trouble. so, i added a statement to select the max and this is what is giving me a problem... if there are no rows in this table, NO data is pulled for a specific record in other tables. here is the problem statement:

and b.i_detail_id = (select max(k.i_detail_id)
from IMT_DET_HST k
where k.jur_id = b.jur_id
and k.fis_yr = b.fis_yr)

i am only interested in the data for the most current (i.e. max ID) record, so I do not want to take this statement out and have multiple records in my view if there are multiple records in the i_detail_hist table. however, i DO want the rest of the information in the view if there are NO records in the i_detail_hist table. any thoughts? (using Oracle8i on Win2000)

for reference, here is the entire create view statement. I just added the ** to easily identify the line in question:

CREATE OR REPLACE VIEW V_USER_APPLICATION AS
select a.CO_SALARY_AVG,
a.CURR_SUBMIT_DT,
a.IDAYS_TTL,
a.IPOP_AVG,
b.TTL_RECORD_NBR,
c.CREATE_DT,
h.STATE_DESC,
(e.J_TYPE_DESC||' of '||d.LEGAL_J_NAME),
f.STATE_TERR_ABB,
l.LAST_NAME,
g.TITLE,
(((b.ttl_record_nbr - b.valid_record_nbr)/b.ttl_record_nbr)*100),
((a.CO_SALARY_AVG/i.mean_amt)*100),
a.jur_id,
a.fis_yr,
n.fis_yr
from FY_APP a,
IMT_DET_HST b,
JU_ST_HST c,
J_PROFILE d,
J_TYPE e,
STATEf,
USER_PROFILE g,
USER_PROFILE l,
J_USER_STATE h,
YEARLY_STATE_MEAN i,
ADDRESS m,
FY_SYSTEM_ACCESS n
where d.j_type_id = e.j_type_id
and a.jur_id = d.jur_id
and f.state_terr_id = m.state_terr_id
and m.address_id = a.j_address_id
and a.c_user_id = g.user_id
and c.jur_id = a.jur_id
and c.fis_yr = a.fis_yr
and c.state_id = (select max(j.state_id)
from JU_ST_HST j
where state_id in (7,8))
and a.jur_id = b.jur_id(+)
and a.fis_yr = b.fis_yr(+)
** and b.i_detail_id = (select max(k.i_detail_id)
from IMT_DET_HST k
where k.jur_id = b.jur_id
and k.fis_yr = b.fis_yr)
and i.state_terr_id = m.state_terr_id
and i.fis_yr = (n.fis_yr - 1)
and h.state_id = a.curr_j_user_state_id
and l.user_id = c.create_id
and n.fis_yr = (select max(fis_yr)
from fy_system_access);


ANY HELP TRUELY APPRECIATED! CAN THIS BE DONE?? THANKS!!

[chrisr]

(sorry, formatting looks much nicer in my message text when typing! didn't come through very nicely!!)