select
    u.user_name,
    u.user_email,
    case
        when max(long.account_key) is not null then 'true'
        else 'false'
    end as [Account Long],
    case
        when max(short.account_key) is not null then 'true'
        else 'false'
    end as [Account Short],
    case
        when max(med.account_key) is not null then 'true'
        else 'false'
    end as [Account Medium]
from
    dbo.tblUser u
    left join dbo.tblUserAccount ua
        on u.user_key = ua.user_key
    left join dbo.tblAccount long
        on (ua.account_key = long.account_key and
            long.account_name = 'Long Term Account')
    left join dbo.tblAccount med
        on (ua.account_key = med.account_key and
            med.account_name = 'Medium Term Account')
    left join dbo.tblAccount short
        on (ua.account_key = short.account_key and
            short.account_name = 'Short Term Account')
group by
    u.user_name,
    u.user_email