Help with query?

Posted on 2010-01-05
Last Modified: 2012-05-08
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')
Question by:jasonbrandt3
    LVL 75

    Accepted Solution

    try removing the where conditions one by one ...
    LVL 31

    Expert Comment

    Can you provide the relevant table structure and the sample data that should produce the approximately 128 records?

    Author Closing Comment

    That was it, one of my conditions was incorrect.
    LVL 31

    Expert Comment

    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?

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now