Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

select nonmatching records

Hi ,
I have two table table AAA and table BBB

AAA                                     BBB
----
key                                  key
                               
511  test2  2                        511  FIRSTRUN


512  aaa    2

513  ccc    2

so based upon key i want to select records from AAA which don't exist in BBB


BBB
511  FIRSTRUN

512  aaa    2

513  ccc    2
0
sam2929
Asked:
sam2929
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:
Personally, I would use an exception join.

e.g.
select a.key
  from AAA a
  exception join BBB b
    on a.key = b.key

Open in new window


HTH,
DaveSlash
0
 
sam2929Author Commented:
Problem is i have to join few more tables and then do exception please look at bottom
three lines and its not liking it i get below error

SQL0104N  An unexpected token "exception" was found following "REA_ACTIVE_INDC ='Y'".  Expected tokens may include:  "<space>



select distinct OC.OFR_MM_ELEMENT_ID as OFF_PREF_KEY ,
DATE(OC.CREATEDATE) as OFF_PREF_EFFDT,
OF.OFF_EXPDT OFF_PREF_EXPDT,
OC.CREATEDATE as OFF_PREF_CRTD_ON,
-10 AS OFF_PREF_CRTD_BY,
OC.UPDATEDATE AS OFF_PREF_LASTUPD_ON,
-10 AS OFF_PREF_LASTUPD_BY,
OF.DIMOFFID,
DM.DIMPREFAREAID as DIMPREFAREAID
from AA01.DIMOFF OF
INNER JOIN OO02.MCT_OO_OFR_MM_ELEMENTS OC
ON OC.MM_OFF_ID = OF.OO_OFF_KEY
and OF.OFF_active_indc = 'Y'
and OF.OFF_lvlnum = 1
inner JOIN AA01.DIMPREF DM
ON OC.OFFING_CODE  = DM.PREF_AREA_L1_CD
and dm.PREF_AREA_ACTIVE_INDC ='Y'
exception join AA01.OFF_PREF b
on OC.OFR_MM_ELEMENT_ID = b.OFF_PREF_KEY
with ur;
0
 
momi_sabagCommented:
try

select distinct OC.OFR_MM_ELEMENT_ID as OFF_PREF_KEY ,
DATE(OC.CREATEDATE) as OFF_PREF_EFFDT,
OF.OFF_EXPDT OFF_PREF_EXPDT,
OC.CREATEDATE as OFF_PREF_CRTD_ON,
-10 AS OFF_PREF_CRTD_BY,
OC.UPDATEDATE AS OFF_PREF_LASTUPD_ON,
-10 AS OFF_PREF_LASTUPD_BY,
OF.DIMOFFID,
DM.DIMPREFAREAID as DIMPREFAREAID
from AA01.DIMOFF OF
INNER JOIN OO02.MCT_OO_OFR_MM_ELEMENTS OC
ON OC.MM_OFF_ID = OF.OO_OFF_KEY
and OF.OFF_active_indc = 'Y'
and OF.OFF_lvlnum = 1
inner JOIN AA01.DIMPREF DM
ON OC.OFFING_CODE  = DM.PREF_AREA_L1_CD
and dm.PREF_AREA_ACTIVE_INDC ='Y'
left outer join AA01.OFF_PREF b
on OC.OFR_MM_ELEMENT_ID = b.OFF_PREF_KEY
where b.off_pref_key is null
with ur;
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now