Oracle SQL - Not Exists - string does not exist in a list of values -


background: spriden_id, sprhold_hldd_code may have 1 or more of several values or no values.

i need select sp.spriden_id, sp.spriden_last_name, sp.spriden_first_name, sr.shrdgmr_seq_no, sr.shrdgmr_program

where (sh.sprhold_hldd_code = 'rh') not exist.

so far, no records returned.

i have found if put code not in list of possible values (such z) in sh.sprhold_hldd_code = 'z', return results.

data: (column names abbreviated) spriden_id   spriden_last   spriden_first  shrdgmr_seq_no   shrdgmr_program sh.sprhold_hldd_code 100001       smith          sue            1                alhe            rh 100001       smith          sue            1                alhe            aa 100001       smith          sue            1                alhe            bb 100005       conners        tim            1                busn            rh 100008       occent         mary           1                math            cc 100008       occent         mary           1                math            aa  expected results: mary record not have rh code.  spriden_id   spriden_last_name   spriden_first_name  shrdgmr_seq_no  shrdgmr_program 100008       occent              mary                1                math

i receive no results though.

code: select sp.spriden_id, sp.spriden_last_name, sp.spriden_first_name, sr.shrdgmr_seq_no,          sr.shrdgmr_program spriden sp join shrdgmr sr on sp.spriden_pidm = sr.shrdgmr_pidm join sprhold sh on sp.spriden_pidm = sh.sprhold_pidm sr.shrdgmr_degs_code = 'pn'   , sr.shrdgmr_term_code_grad >= '201489'   , sp.spriden_change_ind null , not exists (select sh.sprhold_pidm sprhold sh sh.sprhold_hldd_code = 'rh')

to data want, recommend using aggregation having clause:

select sp.spriden_id, sp.spriden_last_name, sp.spriden_first_name, sr.shrdgmr_seq_no,         sr.shrdgmr_program spriden sp join      shrdgmr sr      on sp.spriden_pidm = sr.shrdgmr_pidm join      sprhold sh      on sp.spriden_pidm = sh.sprhold_pidm sr.shrdgmr_degs_code = 'pn' ,       sr.shrdgmr_term_code_grad >= '201489' ,      sp.spriden_change_ind null group sp.spriden_id, sp.spriden_last_name, sp.spriden_first_name, sr.shrdgmr_seq_no,           sr.shrdgmr_program having sum(case when sh.sprhold_hldd_code = 'rh' 1 else 0 end) = 0;

you have 2 problems approach. first subquery either returns true or false , affects rows in original query. want correlated subquery. but, if got right, returning duplicate rows mary. solves both problems.


Comments

Post a Comment

Popular posts from this blog

php - Submit Form Data without Reloading page -

i know question has been asked , answered bunch of times life of me can not solution work on code. believe if redo code, work properly, , in retrospect time i've spent on issue should have redone code...but beyond deadline , have working prototype up... can advise how update multiple forms on single page without reloading page? horrible @ php , complete noob js/jquery/ajax @ best. html/php in single file , believe causing me problems/is largest obstacle @ getting ajax form processing work. basically, want users to: select teams multiple dropdowns in form group 1 (league champs) , on update update db newly selected team without reloadding page. edit team scores per match , on score submit/update, update db new scores. for each requirement there multiple forms (1 each leagues, 1 each per match) have set class on each form id. everything works via php, form submission reloads page each time submitted , not ideal. i have tried ajax code here http://scotch.io/tutoria...
Read more

linux - Rails running on virtual machine in Windows -

the reason why want switch ubuntu people said gems works better on linux on windows. i don't want macbook pro rails development since love windows , cannot stand lack of apps mac. moreover, people saying ubuntu "better" mac: http://www.reddit.com/r/ubuntu/comments/1bgj1s/are_there_advantages_of_ubuntu_over_os_x_for/ does virtual box work enough doesn't have lag @ all? using i7-3517u cpu 8gb memory. need program in environment lag-free , not sluggish. or there alternatives recommend? i unsure permission settings or complicated stuffs regarding terminal. i'll using terminal develop. i'm still quite new here development in linux. please kind me >< thanks. @holyxiaoxin pretty sure should asking on superuser instead. nevertheless, suggest install ubuntu in partition instead of running virtual machine. here's guide https://help.ubuntu.com/community/windowsdualboot also, yes. developing in virtual machine accurate, @ end experiment w...
Read more