Help with query?

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...

select
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

from
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

where
(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')
jasonbrandt3Asked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
try removing the where conditions one by one ...
0
 
awking00Commented:
Can you provide the relevant table structure and the sample data that should produce the approximately 128 records?
0
 
jasonbrandt3Author Commented:
That was it, one of my conditions was incorrect.
0
 
awking00Commented:
So was the query pulling the 22 records that it should have and your initial estimate of 128 records didn't take into account all of the where clauses?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.