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

x
?
Solved

Help with query?

Posted on 2010-01-05
4
Medium Priority
?
183 Views
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...

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')
0
Comment
Question by:jasonbrandt3
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26184530
try removing the where conditions one by one ...
0
 
LVL 32

Expert Comment

by:awking00
ID: 26184560
Can you provide the relevant table structure and the sample data that should produce the approximately 128 records?
0
 

Author Closing Comment

by:jasonbrandt3
ID: 31673153
That was it, one of my conditions was incorrect.
0
 
LVL 32

Expert Comment

by:awking00
ID: 26184728
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

564 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