From: carl.pedersen@dartmouth.edu (L. Carl Pedersen)
Newsgroups: comp.databases.oracle
Subject: Re: How to do this in SQL ?
Date: 31 Aug 92 22:15:52 GMT
Followup-To: comp.databases.oracle
Organization: Dartmouth College

In article <4067@ncrsoph.Sophia.NCR.COM>, sstasuke@ncrsoph.Sophia.NCR.COM
(Steve Stasukewicz) wrote:
> 
> 
> I'm new to SQL, ( and Oracle for that matter) so I'm relying on a little 
> help from the net. ( Actually, there's probably a very easy solution to 
> this. )
> 
> I have the following four tables.  Column names are the same to show
> relationships.  In the 'likes' table, a unique row is identified by a
> combination of all 3 columns.  In the 'item_type' table, a unique row
> is identified by a combination of f1 and f2.  In the other two tables,
> each field is unique to it's column.
> 
> 
> 
>        likes               item            item_type             employee
> 
>  emp_no   f1   f2       f1   txt1        f1   f2  txt2        emp_no   name
>  ------  ---- ----     ---- ------      ---- ---- ----        ------  -----
>   999     10   01       10  apples       10   01  green        999     john
>   999     10   02       20  bananas      10   02  red          123     sue
>   123     30   02       30  beer         20   01  yellow       848     lisa
>   848     20   10                        20   02  brown
>                                          30   01  bud
>                                          30   02  miller
> 
> 
> 
> From the  rows in table 'likes', I should be able to determine that john
> likes green and red apples, sue likes miller beer, and lisa likes
> yellow bananas. (Of course I don't know how to do this yet, but I should
> be able to figure this one out).  My other question is this:
> 
> 1) How can I select all employees who like green apples, but do NOT like
>    miller beer. ( In this case john.) You can tell someone does not like 
>    something by it's absence in the 'likes' table.  To take this a step
>    further, I would like to select all employees who like green apples,
>    miller beer, yellow bananas, and dislikes bud beer and red apples.
> 

I haven't tested the following, but it "ought" to work:

create view likes_v as
select e.emp_no, e.name, i.txt1 thing, t.txt2 kind
  from employee e, 
       likes l,
       item i,
       item_type t
 where e.emp_no = l.emp_no and
       i.f1 = l.f1 and
       t.f1 = l.f1 and t.f2 = l.f2;

select emp_no, name from likes_v
 where kind = 'green' and thing = 'apples'
minus
select emp_no, name from likes_v
 where kind = 'miller' and thing = 'beer';

select emp_no, name from likes_v
 where kind = 'green' and thing = 'apples'
intersect
select emp_no, name from likes_v
 where kind = 'miller' and thing = 'beer'
intersect
select emp_no, name from likes_v
 where kind = 'yellow' and thing = 'bananas'
minus
select emp_no, name from likes_v
 where kind = 'bud' and thing = 'beer'
intersect
select emp_no, name from likes_v
 where kind = 'red' and thing = 'apples';

> 
> Are these tables structured correctly for what I want?  I was also 
> thinking of combining the 'item' and 'item_type' tables into 1 table,
> and having 1 less column in the 'likes' table.  For SQL, is one approach
> more efficient than the other? 
> 

That is a deep question.  Somehow, I have the impression that what you are
giving us is not your real application.  In order to express an intelligent
opinion on your table design, I think I'd need to know more about your
application.
Newsgroups: comp.databases.oracle