From: msolda@lamont.ldgo.columbia.edu (M Solda)
Newsgroups: comp.databases.sybase
Subject: Re: multiple EXISTS subqueries under Sybase 4.8
Keywords: EXISTS, 4.8, sun4, sunos 4.1.1
Date: 16 Nov 92 18:45:40 GMT
Reply-To: msolda@lamont.ldgo.columbia.edu
Organization: Lamont-Doherty Geological Observatory

In article <1e8fj7INN1sg@gazette.bcm.tmc.edu>, mparsons@fleming.csc.bcm.tmc.edu (Mark Parsons) writes:
> 
> In article <1992Nov15.225853.2269@lamont.ldgo.columbia.edu>, msolda@lamont.ldgo.columbia.edu (M Solda) writes:
> |> 
> |> just to follow up, after posting my original article, i solved the problem by
> |> using a UNION operator to join the results into to identical queries, except 
> |> of course, for the EXISTS clauses.
> |> 
> |> i did try "transforming the OR into an AND", but as Dave points out, this would
> |> not work in all cases and this was such a case.
> |> 
> |> anybody know if this is a reported bug?
> |> 
> Ummmmmmm, if you're still working on it, could you post the example
> code?  I'm not quite following this.
> 

okay, here it is:

/******************************************************************************/
/*                                                                            */
/*  SQL query to extract data about employees in the available titles         */
/*                                                                            */
/*  NOTE:  the EXISTS and NOT EXISTS query seem not to be able to be joined   */
/*         in a single where and thus the first query finds those employees   */
/*         that have only been employeed once, while the second query finds   */
/*         those employees who have been employeed more than once.  the       */
/*         results are UNIONED together into a single query result.           */
/*                                                                            */
/******************************************************************************/

/* for those who have been employeed only once */

select last_name, first_name, salary, title
from employee_id i1, salary_history s1, employee_status e1
where not exists (select *
                  from employee_status
                  where status_begin < '1/1/0'
                     and ss_num = s1.ss_num)
   and salary_scope_begin = (select max(salary_scope_begin)
                             from salary_history
                             where ss_num = s1.ss_num
                                and salary_scope_begin <= '1/1/0')
   and employee_id.ss_num = salary_history.ss_num
   and employee_id.ss_num = employee_status.ss_num
   and status_begin is NULL
   and title_effective_date = (select max(title_effective_date)
                               from employee_status
                               where title_effective_date <= '1/1/0'
                                  and ss_num = s1.ss_num)

/* use the UNION operator to combine the two selects into a single result */

union

/* for those who have been employeed more than once */

select last_name, first_name, salary, title
from employee_id i1, salary_history s1, employee_status e1
where exists (select *
              from salary_history
              where ss_num = s1.ss_num
                 and comment = 'Employment'
                 and salary_scope_begin <= '1/1/0'
                 and salary_scope_begin > (select max(status_begin)
                                           from employee_status
                                           where status_begin < '1/1/0'
                                              and ss_num = s1.ss_num))
   and salary_scope_begin = (select max(salary_scope_begin)
                             from salary_history
                             where ss_num = s1.ss_num
                                and salary_scope_begin <= '1/1/0')
   and employee_id.ss_num = salary_history.ss_num
   and employee_id.ss_num = employee_status.ss_num
   and status_begin is NULL
   and title_effective_date = (select max(title_effective_date)
                               from employee_status
                               where title_effective_date <= '1/1/0'
                                  and ss_num = s1.ss_num)
order by title, last_name
compute avg(salary), count(title) by title
compute avg(salary), count(title)

msolda