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)

;

• Commands Entered ------------------------- WITH Ordered_MAP AS ( SELECT ITEMID , CATEGORYID , ROWNUMBER() OVER(PARTITION BY ITEMID ORDER BY CATEGORYID) rn FROM ITEM_CATEGORY_MAP )
  ,Recurse (rn, itemid, itemname, categories) AS (
  SELECT 0, itemid, itemname, CAST('' AS VARCHAR(100)) FROM ITEMS UNION ALL SELECT pre.rn + 1 , pre.itemid , pre.itemname , pre.categories||', ' ||SUBSTR(DIGITS(c.categoryid),3,3)||':'||c.categoryname FROM Recurse pre , Ordered_MAP new , CATEGORY c WHERE pre.rn < 10000 AND new.itemid = pre.itemid AND new.rn = pre.rn + 1 AND c.categoryid = new.categoryid ) SELECT itemid , itemname , SUBSTR(categories,3) AS categories FROM Recurse r WHERE rn = (SELECT MAX(rn) FROM Recurse rm WHERE rm.itemid = r.itemid ) ORDER BY itemid ;

  ---

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