Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!swrinde!cs.utexas.edu!uunet!fernwood!oracle!news
From: cyeung@uk.oracle.com (Charles Yeung)
Newsgroups: comp.databases
Subject: Re: Division in embedded sql
Keywords: Division, ORACLE
Message-ID: <1991Mar18.150721.16961@oracle.com>
Date: 18 Mar 91 15:07:21 GMT
References: <18578@cs.utexas.edu>
Sender: news@oracle.com
Organization: Oracle Corp.
Lines: 93

ubiquity@cs.utexas.edu (Richard Hoffman) writes:

>In article <1991Mar4.211343.22513@cs.wayne.edu> nis@artemis.cs.wayne.edu
>(Nitin Shah) wrote:
>>>emp                      Project     
>>>    EmpNo    Project         Pno   Pname
>>>    1000     1               1     a
>>>    1000     2               2     b
>>>    1000     3               3     c
>>>    1001     1 
>>>    1002     1 
>>>    1002     2 
>>> 
>>>query: 
>>>    Give EmpNo of employees who work on project `a` and `b`.
>>> 
>>>Answer: 
>>>    EmpNo 
>>>    1000 
>>>    1002 
> 
>In article <1991Mar5.220941.25393@oracle.com> mmorris@oracle.UUCP (Martin
>Morris) suggests the following:

>>SELECT DISTINCT e.empno
>>FROM   empno e,
>>       project p
>>WHERE  p.pname = 'a'
>>OR     p.pname = 'b'
>>AND    p.Pno = e.Project
>>ORDER BY e.empno

>But this will give the EmpNo of employees who work on *either* a and b, not
>*both* a and b.  For instance, 1001 would be included in the answer set,
>because he/she works on project a (but not b).

>A slightly tricky (because it doesn't really use division) approach 
>which gets the right answer is:

>	SELECT   e.empno
>	FROM     empno e, project p
>	WHERE    p.Pno = e.Project AND p.pname IN ('a','b')
>	GROUP BY e.empno
>	HAVING   count(*) = 2

>I haven't actually tried this out, so I can't guarantee that it works,
>but you get the idea.  This or something like it ought to work.

>Another possibility:

>	SELECT e.empno
>	FROM   empno e
>	WHERE  e.empno IN (SELECT k.empno
>			   FROM   empno k, project p
>			   WHERE  p.Pno = k.Project AND p.pname = 'a')
>	AND    e.empno IN (SELECT k.empno
>			   FROM   empno k, project p
>			   WHERE  p.Pno = k.Project AND p.pname = 'b')

>Yet another possibility:

>	SELECT e.empno
>	FROM   empno e, project p
>	WHERE  p.Pno = e.Project AND p.pname = 'a'

>	INTERSECT

>	SELECT e.empno
>	FROM   empno e, project p
>	WHERE  p.Pno = e.Project AND p.pname = 'b'

>I don't know whether Oracle has INTERSECT, but it's part of SQL2.

>Chris Date shows how to simulate the division operator using EXISTS (or
>maybe one of the quantified predicates) in several of his books, but I can
>never remember how to do it.

>-- 
>Richard Hoffman       IBM Personal Systems Database Development   (512) 823-1822
>1529 Ben Crenshaw Way
>Austin, TX 78746         "Life is a gamble at terrible odds; 
>(512) 327-9232            if it were a bet you wouldn't take it"  (Tom Stoppard)

------------------------------------------------------------------------------

YES ! Oracle has INTERSECT , MINUS and UNION.

-------------------------------------------------------------------------------
Charles Yeung				     Internet : cyeung@uk.oracle.com
Oracle EDC, Oracle Park,                  Oracle*Mail : CYEUNG on UKPYR2
Bittams Lane, Guildford Road,                   Phone : 093287.2020 X 2167
Chertsey, Surrey  KT16 9RG, UK                    FAX : 093287.3293
-------------------------------------------------------------------------------