joining the where clause to select statement of two different tables

Question

How can I properly structure the below query to make it work? I would like to have the Ref_CD = MBR_ID_TYPE_ID to be my select statement on the beginning of it.

select MBR_ID_TYP_ID || '-' || (
    select ref_desc 
    from ref 
    where ref_nm='memberIDType' 
        and REF_CD = MBR_ID_TYP_ID) as "Member Type", id.* 
from mbr_id id 
where mbr_id = 0989879;

Answer 1

I'm not too sure what you are doing there and that's not at all how I write queries. Either way, I would try it this way:

select 
    a.MBR_ID_TYP_ID || '-' || b.ref_desc as "Member Type" , 
    a.*
from mbr_id as a
    left outer join ref as b 
    on  b.ref_nm = 'memberIDType' 
    and a.REF_CD = b.MBR_ID_TYP_ID    -- or: b.REF_CD = a.MBR_ID_TYP_ID 
                                      -- not clear from the code
where 
    a.mbr_id = 6484532;

I don't join in where clauses because that is really hard to read. I generally put the columns in the select portion, then do the joins in the from section. The filtering goes on in the where clause. Finally if necessary the order by is last, if I need to sort.