oracle - SQL Inner join in a nested select statement -

i'm trying inner join in nested select statement. basically, there first , last reason ids produce number (ex: 200). in table, there definitions ids. i'm trying pull last id, along corresponding comment whatever pulled (ex: 200 - patient cancelled), first id , comment whatever id is.

this have far:

select busn_id     area_name     date     area_status (select b.reason_id       a.last_reason_id       busn_info a, busn_reasons b     a.last_reason _id=b.reason_id,     (select b.reason_id           a. first_reason_id         busn_info a, busn_reasons b         a_first_reason_id = b.reason_id) busn_info  

i believe inner join best, i'm stuck on how work.

required result (this example dummy data):

first id -- busn reason --       last id -- busn reason 1           patient sick          2          patient cancelled 2           patient cancelled     2          patient cancelled 3           patient no show       1          patient sick 

justin_cave's second example way used solve problem.

if want use inline select statements, inline select has select single column , should join table basis of query. in query posted, you're selecting same numeric identifier multiple times. guess want query string column lookup table-- i'll assume column called reason_description

select busn_id,        area_name,        date,        area_status,        a.last_reason_id,        (select b.reason_description           busn_reasons b          a.last_reason_id=b.reason_id),         a.first_reason_id,        (select b.reason_description           busn_reasons b          a.first_reason_id = b.reason_id) busn_info 

more conventionally, though, you'd join busn_reasons table twice

select i.busn_id,        i.area_name,        i.date,        i.area_status,        i.last_reason_id,        last_reason.reason_description,        i.first_reason_id,        first_reason.reason_description   busn_info        join busn_reason first_reason          on( i.first_reason_id = first_reason.reason_id )        join busn_reason last_reason          on( i.last_reason_id = last_reason.reason_id )