How to do this in SQL ?
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:
- 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_at_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".
--Received on Fri Aug 28 1992 - 17:09:40 CEST