Eliminating duplicate values-CodePudding

select distinct
    FA_ADD.ASSET_ID,
    FA_ADD.ASSET_NUMBER,
    PPA.SEGMENT1,
    fa_add.DESCRIPTION
from 
    apps.FA_ADDITIONS FA_ADD
left join
    apps.FA_ASSET_INVOICES FAI on FAI.ASSET_ID = Fa_Add.ASSET_NUMBER
left join
    apps.PA_PROJECTS_ALL PPA on PPA.PROJECT_ID = FAI.PROJECT_ID
where 
    FA_ADD.ASSET_NUMBER in ('110011427', '110011425', '100004472', '110011480')
order by 
    FA_ADD.ASSET_ID;

Please help me with this query.

Output

In the example output above, I need the rows 1, 2, 4, 6

CodePudding user response：

I suppose you miss some condition on join to table PA_PROJECTS_ALL, because result contains null value on segment column from this table in results

CodePudding user response：

You have to add conditions in the left outer join PA_PROJECTS_ALL to be more precise and not have duplicate lines.

try something like this

select distinct
    FA_ADD.ASSET_ID,
    FA_ADD.ASSET_NUMBER,
    PPA.SEGMENT1,
    fa_add.DESCRIPTION
    from apps.FA_ADDITIONS FA_ADD
    LEFT JOIN apps.FA_ASSET_INVOICES FAI on FAI.ASSET_ID = Fa_Add.ASSET_NUMBER
    LEFT JOIN apps.PA_PROJECTS_ALL PPA ON PPA.PROJECT_ID = FAI.PROJECT_ID and proj_num is not null
    where FA_ADD.ASSET_NUMBER in ('110011427','110011425','100004472','110011480')
    order by FA_ADD.ASSET_ID