NetNews Usenet Archive 1992 #19/NN_1992_19.iso/spool/comp/database/oracle/1441

Internet Message Format | 1992-08-31 | 3.6 KB

Path: sparky!uunet!decwrl!bu.edu!dartvax!kip-sn-49.dartmouth.edu!user
From: [e-mail] (L. Carl Pedersen)
Newsgroups: comp.databases.oracle
Subject: Re: How to do this in SQL ?
Message-ID: <[e-mail]>
Date: 31 Aug 92 22:15:52 GMT
References: <[e-mail]>
Sender: [e-mail] (The News Manager)
Followup-To: comp.databases.oracle
Organization: Dartmouth College
Lines: 85

In article <[e-mail]>, [e-mail]
(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.