home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #27 / NN_1992_27.iso / spool / comp / database / sybase / 329 < prev    next >
Encoding:
Text File  |  1992-11-17  |  4.6 KB  |  98 lines
  1. Path: sparky!uunet!charon.amdahl.com!pacbell.com!ames!sun-barr!cs.utexas.edu!rutgers!news.columbia.edu!lamont!msolda
  2. From: msolda@lamont.ldgo.columbia.edu (M Solda)
  3. Newsgroups: comp.databases.sybase
  4. Subject: Re: multiple EXISTS subqueries under Sybase 4.8
  5. Keywords: EXISTS, 4.8, sun4, sunos 4.1.1
  6. Message-ID: <1992Nov16.184540.11698@lamont.ldgo.columbia.edu>
  7. Date: 16 Nov 92 18:45:40 GMT
  8. 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>
  9. Sender: news@lamont.ldgo.columbia.edu
  10. Reply-To: msolda@lamont.ldgo.columbia.edu
  11. Organization: Lamont-Doherty Geological Observatory
  12. Lines: 84
  13.  
  14. In article <1e8fj7INN1sg@gazette.bcm.tmc.edu>, mparsons@fleming.csc.bcm.tmc.edu (Mark Parsons) writes:
  15. > 
  16. > In article <1992Nov15.225853.2269@lamont.ldgo.columbia.edu>, msolda@lamont.ldgo.columbia.edu (M Solda) writes:
  17. > |> 
  18. > |> just to follow up, after posting my original article, i solved the problem by
  19. > |> using a UNION operator to join the results into to identical queries, except 
  20. > |> of course, for the EXISTS clauses.
  21. > |> 
  22. > |> i did try "transforming the OR into an AND", but as Dave points out, this would
  23. > |> not work in all cases and this was such a case.
  24. > |> 
  25. > |> anybody know if this is a reported bug?
  26. > |> 
  27. > Ummmmmmm, if you're still working on it, could you post the example
  28. > code?  I'm not quite following this.
  29. > 
  30.  
  31. okay, here it is:
  32.  
  33. /******************************************************************************/
  34. /*                                                                            */
  35. /*  SQL query to extract data about employees in the available titles         */
  36. /*                                                                            */
  37. /*  NOTE:  the EXISTS and NOT EXISTS query seem not to be able to be joined   */
  38. /*         in a single where and thus the first query finds those employees   */
  39. /*         that have only been employeed once, while the second query finds   */
  40. /*         those employees who have been employeed more than once.  the       */
  41. /*         results are UNIONED together into a single query result.           */
  42. /*                                                                            */
  43. /******************************************************************************/
  44.  
  45. /* for those who have been employeed only once */
  46.  
  47. select last_name, first_name, salary, title
  48. from employee_id i1, salary_history s1, employee_status e1
  49. where not exists (select *
  50.                   from employee_status
  51.                   where status_begin < '1/1/0'
  52.                      and ss_num = s1.ss_num)
  53.    and salary_scope_begin = (select max(salary_scope_begin)
  54.                              from salary_history
  55.                              where ss_num = s1.ss_num
  56.                                 and salary_scope_begin <= '1/1/0')
  57.    and employee_id.ss_num = salary_history.ss_num
  58.    and employee_id.ss_num = employee_status.ss_num
  59.    and status_begin is NULL
  60.    and title_effective_date = (select max(title_effective_date)
  61.                                from employee_status
  62.                                where title_effective_date <= '1/1/0'
  63.                                   and ss_num = s1.ss_num)
  64.  
  65. /* use the UNION operator to combine the two selects into a single result */
  66.  
  67. union
  68.  
  69. /* for those who have been employeed more than once */
  70.  
  71. select last_name, first_name, salary, title
  72. from employee_id i1, salary_history s1, employee_status e1
  73. where exists (select *
  74.               from salary_history
  75.               where ss_num = s1.ss_num
  76.                  and comment = 'Employment'
  77.                  and salary_scope_begin <= '1/1/0'
  78.                  and salary_scope_begin > (select max(status_begin)
  79.                                            from employee_status
  80.                                            where status_begin < '1/1/0'
  81.                                               and ss_num = s1.ss_num))
  82.    and salary_scope_begin = (select max(salary_scope_begin)
  83.                              from salary_history
  84.                              where ss_num = s1.ss_num
  85.                                 and salary_scope_begin <= '1/1/0')
  86.    and employee_id.ss_num = salary_history.ss_num
  87.    and employee_id.ss_num = employee_status.ss_num
  88.    and status_begin is NULL
  89.    and title_effective_date = (select max(title_effective_date)
  90.                                from employee_status
  91.                                where title_effective_date <= '1/1/0'
  92.                                   and ss_num = s1.ss_num)
  93. order by title, last_name
  94. compute avg(salary), count(title) by title
  95. compute avg(salary), count(title)
  96.  
  97. msolda
  98.