How to do this in SQL ?

From: Steve Stasukewicz <sstasuke_at_ncrsoph.Sophia.NCR.COM>
Date: 28 Aug 92 15:09:40 GMT
Message-ID: <4067_at_ncrsoph.Sophia.NCR.COM>

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.


Steve.Stasukewicz_at_sophia.NCR.FR GO HAWKS ! GO HAWKS ! GO HAWKS ! NCR Europe Group ISS/ECS
Espaces De Sophia
25 Route de Lucioles                   Tel. (33)
06560 Valbonne FRANCE                  Fax. (33)

"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".

Received on Fri Aug 28 1992 - 17:09:40 CEST

Original text of this message