CODEWITHSUNDEEP
sqloracle-sqldeveloper
Ting
Ting

Reputation: 1

Eliminating duplicate values

select distinct
    FA_ADD.ASSET_ID,
    FA_ADD.ASSET_NUMBER,
    PPA.SEGMENT1
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','100004641')
order by 
    FA_ADD.ASSET_ID;

Please help me with this query.

Output

In the example output above, I need the rows 1, 2, 3, 5, 7

Upvotes: 0

Views: 121

Answers (1)

Matteo
Matteo

Reputation: 1

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

Upvotes: 0

Related Questions