在TSQL查询消除空行[复制](Eliminating NULL rows in TSQL quer

这个问题已经在这里有一个答案：

可能重复：
如何消除TSQL NULL字段

我使用SSMS 2008 R2和正在开发一个TSQL查询。我想只有1记录/ PROFILE_NAME。因为一些值是NULL，我目前在做LEFT上大部分表的连接。但与LEFT JOIN的问题是，现在我得到> 1点的记录对于一些profile_names！

但是，如果我改变了为内部连接，然后一些profile_names被完全排除，因为他们有这些列的NULL值。如何限制查询结果只有一个记录/ PROFILE_NAME无论NULL值？如果有非NULL值，那么我希望它选择与非NULL值的记录。这里是初始查询：

select distinct
        gp.group_profile_id,
        gp.profile_name,
        gp.license_number,
        gp.is_accepting,
        case when gp.is_accepting = 1 then 'Yes'
            when gp.is_accepting = 0 then 'No '
            end as is_accepting_placement,
        mo.profile_name as managing_office,
        regions.[region_description] as region,     
        pv.vendor_name,
        pv.id as vendor_id,
        at.description as applicant_type,
        dbo.GetGroupAddress(gp.group_profile_id, null, 0) as [Office Address],
        gsv.status_description
from  group_profile gp With (NoLock)
    inner join group_profile_type gpt With (NoLock) on gp.group_profile_type_id = gpt.group_profile_type_id and gpt.type_code = 'FOSTERHOME' and gp.agency_id = @agency_id and gp.is_deleted = 0
    inner join group_profile mo With (NoLock) on gp.managing_office_id = mo.group_profile_id
    left outer join payor_vendor pv With (NoLock) on gp.payor_vendor_id = pv.payor_vendor_id
    left outer join applicant_type at With (NoLock) on gp.applicant_type_id = at.applicant_type_id and at.is_foster_home = 1
    inner join group_status_view gsv With (NoLock) on gp.group_profile_id = gsv.group_profile_id and gsv.status_value = 'OPEN' and gsv.effective_date =  
    (Select max(b.effective_date) from  group_status_view b  With (NoLock)
    where gp.group_profile_id = b.group_profile_id)
    left outer join regions With (NoLock) on isnull(mo.regions_id, gp.regions_id) = regions.regions_id
    left join enrollment en on en.group_profile_id = gp.group_profile_id
    join event_log el on el.event_log_id = en.event_log_id
    left join people client on client.people_id = el.people_id

正如你所看到的，上面查询的结果是1行/ PROFILE_NAME：

group_profile_id    profile_name    license_number  is_accepting    is_accepting_placement  managing_office region  vendor_name vendor_id   applicant_type  Office Address  status_description  Cert Date2

但现在看，当我在2 LEFT JOIN的添加和1个附加列发生了什么：

select distinct
        gp.group_profile_id,
        gp.profile_name,
        gp.license_number,
        gp.is_accepting,
        case when gp.is_accepting = 1 then 'Yes'
             when gp.is_accepting = 0 then 'No '
            end as is_accepting_placement,
        mo.profile_name as managing_office,
        regions.[region_description] as region,     
        pv.vendor_name,
        pv.id as vendor_id,
        at.description as applicant_type,
        dbo.GetGroupAddress(gp.group_profile_id, null, 0) as [Office Address],
        gsv.status_description,
            ri.[description] as race
from  group_profile gp With (NoLock)
    inner join group_profile_type gpt With (NoLock) on gp.group_profile_type_id = gpt.group_profile_type_id and gpt.type_code = 'FOSTERHOME' and gp.agency_id = @agency_id and gp.is_deleted = 0
    inner join group_profile mo With (NoLock) on gp.managing_office_id = mo.group_profile_id
    left outer join payor_vendor pv With (NoLock) on gp.payor_vendor_id = pv.payor_vendor_id
    left outer join applicant_type at With (NoLock) on gp.applicant_type_id = at.applicant_type_id and at.is_foster_home = 1
    inner join group_status_view gsv With (NoLock) on gp.group_profile_id = gsv.group_profile_id and gsv.status_value = 'OPEN' and gsv.effective_date =  
    (Select max(b.effective_date) from  group_status_view b  With (NoLock)
    where gp.group_profile_id = b.group_profile_id)
    left outer join regions With (NoLock) on isnull(mo.regions_id, gp.regions_id) = regions.regions_id
    left join enrollment en on en.group_profile_id = gp.group_profile_id
    join event_log el on el.event_log_id = en.event_log_id
    left join people client on client.people_id = el.people_id
    left join race With (NoLock) on el.people_id = race.people_id
    left join race_info ri with (nolock) on ri.race_info_id = race.race_info_id

上面的查询结果都一样profile_names的，但有些与NULL值的比赛：

group_profile_id    profile_name    license_number  is_accepting    is_accepting_placement  managing_office region  vendor_name vendor_id   applicant_type  Office Address  status_description  Cert Date2  race

不幸的是，复杂化，我需要在2个额外的表来参加这一个额外的字段值（种族）的事项。如果我只是改变了过去两年LEFT JOIN的上方为内部连接，然后我消除上面的空行。但我也消除一些profile_names的：

group_profile_id    profile_name    license_number  is_accepting    is_accepting_placement  managing_office region  vendor_name vendor_id   applicant_type  Office Address  status_description  Cert Date2  race

希望我提供了所有的，你需要为这个问题的细节。