From: sstasuke@ncrsoph.Sophia.NCR.COM (Steve Stasukewicz)
Newsgroups: comp.databases.oracle
Subject: How to do this in SQL ?
Date: 28 Aug 92 15:09:40 GMT
Reply-To: sstasuke@ncrsoph.Sophia.NCR.COM (Steve Stasukewicz)
Distribution: world
Organization: NCR European Development Center


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.


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? 


       new_item                                    likes

   c1    c2        c3                          emp_no   c1 
  ---   ----     ------                        ------  ----
   10   green    apples                          999    10
   11   red      apples                          999    11
   21   yellow   bananas                         848    21
   22   brown    bananas



Of course with this structure, the replicated data is a variable length
character field instead of a 2 or 3 digit number.   

Any comments, advice, solutions would be appreciated.  

Ciao

--------------------------------------------------------------------------------
Steve.Stasukewicz@sophia.NCR.FR        GO HAWKS !  GO HAWKS !  GO HAWKS !
NCR Europe Group ISS/ECS
Espaces De Sophia
25 Route de Lucioles                   Tel. (33) 92.94.45.12
06560 Valbonne FRANCE                  Fax. (33) 92.94.45.45

"Operator, I'd like to make a long distance call to Iowa".
"I'm sorry sir, all lines are busy.  Would you like to hold"?
"WOULD I LIKE TO HOLD?!!  WOULD I LIKE TO HOLD ??!!!!  Yea, I'll hold".
--