Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Many To Many Relationships

Re: Many To Many Relationships

From: Tonkuma <tonkuma_at_jp.ibm.com>
Date: 17 Apr 2007 08:58:36 -0700
Message-ID: <1176825516.368798.123280@n59g2000hsh.googlegroups.com>


I hope this example to give you some hints for resolving your problem. (I'm using DB2. Syntax of Recursive query may be different on other DBMS.)
CREATE TABLE ITEMS
(itemid SMALLINT NOT NULL
,itemname VARCHAR(15) NOT NULL
,PRIMARY KEY (itemid)

);

CREATE TABLE CATEGORY
(categoryid SMALLINT NOT NULL
,categoryname VARCHAR(10) NOT NULL
,PRIMARY KEY (categoryid)

);

CREATE TABLE ITEM_CATEGORY_MAP
(itemid SMALLINT NOT NULL
,categoryid SMALLINT NOT NULL
,PRIMARY KEY (itemid, categoryid)

);

INSERT INTO ITEMS
VALUES

 (1, 'Apple')

,(2, 'Banana')
,(3, 'Coconut')
,(4, 'Peanut')
,(5, 'Pimiento')
,(6, 'Strawberry')
,(7, 'Tomato')

;

INSERT INTO CATEGORY
VALUES

 (1, 'Plant')

,(2, 'Tree')
,(3, 'Fruit')
,(4, 'Vegitable')
,(5, 'Green')
,(6, 'Red')
,(7, 'Yellow')
,(8, 'Brown')

;

INSERT INTO ITEM_CATEGORY_MAP
VALUES

 (1, 2)

,(1, 3)
,(1, 6)
,(2, 2)
,(2, 3)
,(2, 4)
,(2, 7)
,(3, 2)
,(3, 4)
,(3, 8)
,(4, 1)
,(4, 3)
,(4, 4)
,(4, 8)
,(5, 1)
,(5, 4)
,(5, 5)
,(5, 6)
,(5, 7)
,(6, 1)
,(6, 3)
,(6, 6)
,(7, 1)
,(7, 3)
,(7, 4)
,(7, 6)

;

ITEMID ITEMNAME CATEGORIES
------ ---------------


     1 Apple           002:Tree, 003:Fruit, 006:Red
     2 Banana          002:Tree, 003:Fruit, 004:Vegitable, 007:Yellow
     3 Coconut         002:Tree, 004:Vegitable, 008:Brown
     4 Peanut          001:Plant, 003:Fruit, 004:Vegitable, 008:Brown
     5 Pimiento        001:Plant, 004:Vegitable, 005:Green, 006:Red,
007:Yellow
     6 Strawberry      001:Plant, 003:Fruit, 006:Red
     7 Tomato          001:Plant, 003:Fruit, 004:Vegitable, 006:Red

  7 record(s) selected. Received on Tue Apr 17 2007 - 10:58:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US