SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Union two Statements, Exclude from results if ID included on first Statement.

enriquemansJun 21 2023

Hello, I have 2 Oracle SQL Statements that pulls records containing 2 address Types (Alternative and Home).

Most do not have an alternative address, but if they do, I need a way to exclude the ID from the second query (Home Address).

I tried Exclude and Minus and didnt get good results. Any Ideas would be appreciated

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

--Alternative Address Query.
SELECT sbsb.ID
,sbad.SBAD_TYPE
,sbad.SBAD_ADDR1
,sbad.SBAD_ADDR2
,sbad.SBAD_ADDR3
,sbad.SBAD_CITY
,sbad.SBAD_STATE
,sbad.SBAD_ZIP
FROM SUBSC sbsb
INNER JOIN ADDR sbad ON sbad.SBSB_CK = sbsb.SBSB_CK
WHERE sbad.SBAD_TYPE = 'Alternative'

UNION

--Home Address Statement

SELECT sbsb2.ID
,sbad2.SBAD_TYPE
,sbad2.SBAD_ADDR1
,sbad2.SBAD_ADDR2
,sbad2.SBAD_ADDR3
,sbad2.SBAD_CITY
,sbad2.SBAD_STATE
,sbad2.SBAD_ZIP
FROM SUBSC sbsb2
INNER JOIN ADDR sbad2 ON sbad2.SBSB_CK = sbsb2.SBSB_CK
WHERE sbad.SBAD_TYPE = 'Home'

This post has been answered by Solomon Yakobson on Jun 21 2023

Jump to Answer

Added on Jun 21 2023

9 comments

1,832 views

1 mention