home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #27 / NN_1992_27.iso / spool / comp / database / sybase / 362 < prev    next >
Encoding:
Text File  |  1992-11-19  |  3.0 KB  |  66 lines
  1. Newsgroups: comp.databases.sybase
  2. Path: sparky!uunet!paladin.american.edu!darwin.sura.net!zaphod.mps.ohio-state.edu!sol.ctr.columbia.edu!news.columbia.edu!lamont!msolda
  3. From: msolda@lamont.ldgo.columbia.edu (M Solda)
  4. Subject: Re: multiple EXISTS subqueries under Sybase 4.8
  5. Message-ID: <1992Nov19.174435.25700@lamont.ldgo.columbia.edu>
  6. Keywords: EXISTS, 4.8, sun4, sunos 4.1.1
  7. Sender: news@lamont.ldgo.columbia.edu
  8. Reply-To: msolda@lamont.ldgo.columbia.edu
  9. Organization: Lamont-Doherty Geological Observatory
  10. References: <1992Nov14.174847.29576@lamont.ldgo.columbia.edu> <1992Nov15.144258.18635@panix.com> <1992Nov15.225853.2269@lamont.ldgo.columbia.edu> <1e8fj7INN1sg@gazette.bcm.tmc.edu> <1992Nov16.184540.11698@lamont.ldgo.columbia.edu> <1ee852INN90m@gazette.bcm.tmc.edu>
  11. Date: Thu, 19 Nov 1992 17:44:35 GMT
  12. Lines: 52
  13.  
  14. In article <1ee852INN90m@gazette.bcm.tmc.edu>, mparsons@fleming.csc.bcm.tmc.edu (Mark Parsons) writes:
  15. > 
  16. > In article <1992Nov16.184540.11698@lamont.ldgo.columbia.edu>, msolda@lamont.ldgo.columbia.edu (M Solda) writes:
  17. > |> > Ummmmmmm, if you're still working on it, could you post the example
  18. > |> > code?  I'm not quite following this.
  19. > |> > 
  20. > |> 
  21. > |> okay, here it is:
  22. > |> 
  23. > 
  24. > <UNIONed EXISTS and NOT EXISTS code deleted>
  25. > 
  26. > *blush* I should've said "post the original problem code", i.e., 
  27. > not the union version . . . 
  28.  
  29. here is the query that would not work:
  30.  
  31.  
  32.    select last_name, first_name, salary, title
  33.    from employee_id i1, salary_history s1, employee_status e1
  34.    where (
  35.              (exists (select *
  36.                       from salary_history
  37.                       where ss_num = s1.ss_num
  38.                          and comment = 'Employment'
  39.                          and salary_scope_begin <= '9/1/2'
  40.                          and salary_scope_begin > (select max(status_begin)
  41.                                                    from employee_status
  42.                                                    where status_begin < '9/1/2'
  43.                                                       and ss_num = s1.ss_num)))
  44.           or
  45.              (not exists (select *
  46.                           from employee_status
  47.                           where status_begin < '9/1/2'
  48.                              and ss_num = s1.ss_num))
  49.           )
  50.       and salary_scope_begin = (select max(salary_scope_begin)
  51.                                 from salary_history
  52.                                 where ss_num = s1.ss_num
  53.                                    and salary_scope_begin <= '9/1/2')
  54.       and employee_id.ss_num = salary_history.ss_num
  55.       and employee_id.ss_num = employee_status.ss_num
  56.       and status_begin is NULL
  57.       and title_effective_date = (select max(title_effective_date)
  58.                                   from employee_status
  59.                                   where title_effective_date <= '9/1/2'
  60.                                      and ss_num = s1.ss_num)
  61.    order by title, last_name
  62.    compute avg(salary), count(title) by title
  63.    compute avg(salary), count(title)
  64.  
  65. msolda
  66.