Path: utzoo!news-server.csri.toronto.edu!cs.utexas.edu!ubiquity
From: ubiquity@cs.utexas.edu (Richard Hoffman)
Newsgroups: comp.databases
Subject: Re: Division in embedded sql
Summary: works on 'a' AND 'b', not 'a' OR 'b'
Keywords: Division, ORACLE
Message-ID: <18578@cs.utexas.edu>
Date: 17 Mar 91 16:08:19 GMT
Organization: Ubiquitous Productions, R. Hoffman Proprietor
Lines: 80

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)