Re: Many To Many Relationships
Date: 17 Apr 2007 08:58:36 -0700
Message-ID: <1176825516.368798.123280_at_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'),(6, 'Strawberry')
,(2, 'Banana')
,(3, 'Coconut')
,(4, 'Peanut')
,(5, 'Pimiento')
,(7, 'Tomato')
;
INSERT INTO CATEGORY
VALUES
(1, 'Plant'),(7, 'Yellow')
,(2, 'Tree')
,(3, 'Fruit')
,(4, 'Vegitable')
,(5, 'Green')
,(6, 'Red')
,(8, 'Brown')
;
INSERT INTO ITEM_CATEGORY_MAP
VALUES
(1, 2),(7, 4)
,(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, 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 - 17:58:36 CEST