Help with query?
Posted on 2010-01-05
Need help with my logic..I need to find records who do not have a certain action code on them. For example there are actions codes in the items table, I need to find the records for the criteria in the where clause for the cdy table where the items.action code is not on these records. There should be approximately 128 and my query is only pulling 22! Here is what I wrote, but not correct...
cdy.id_num, nm.last_name, nm.first_name, nm.middle_name,
cdy.yr_cde, cdy.trm_cde, cdy.prog_cde, cdy.div_cde, cdy.loca_cde,
cdy.stage, cdy.load_p_f, cdy.candidacy_type, cd.last_org_attend,
it.action_code, it.item_description, ad.gpa
candidacy cdy left outer join name_master nm on cdy.id_num = nm.id_num
left outer join candidate cd on cd.id_num = nm.id_num
left outer join items it on it.id_number = cd.id_num
left outer join ad_org_tracking ad on ad.id_num = it.id_number
(cdy.cur_candidacy = 'Y' and cdy.stage in ('UACEP','UREG','UDEPS','DRACC','DRCMM','DRDEP','DRREG') and cdy.candidacy_type = 'F' ) and
(cdy.yr_cde = '1011' and cdy.trm_cde = 'FA') and
(ad.gpa >= 2.7) and
it.id_number not in (select items.id_number from items where items.action_code = 'SRACSC')