Many To Many Relationships
From: Loon <skarl.the.giraffe_at_gmail.com>
Date: 16 Apr 2007 03:54:42 -0700
Message-ID: <1176720882.931603.161720_at_q75g2000hsh.googlegroups.com>
Hi everyone,
Is there any way to return each ITEM once, with all CATEGORIES shown? My own knowledge of database theory say no, and that to do this, I would need several database calls - one for the ITEM list, then another call for each ITEM to get the category list. Am I missing something fundamental here? Received on Mon Apr 16 2007 - 12:54:42 CEST
Date: 16 Apr 2007 03:54:42 -0700
Message-ID: <1176720882.931603.161720_at_q75g2000hsh.googlegroups.com>
Hi everyone,
Firstly this is theoretical situation where I have no DDL to show you, I'm trying to get my head around how it works. I have a situation where I have a many-to-many relationship between ITEMS and CATEGORIES, with a simple table in between holding ItemID and CategoryID where the two are related (called ITEM_CATEGORY_MAP)
I have to display a list of ITEMs, with the list showing all
CATEGORIES that item belongs to. To put it simply, I've tried:
SELECT * FROM ITEMS AS I
(INNER JOIN ITEM_CATEGORY_MAP AS ICM
ON I.ITEMID = ICM.ITEMID)
INNER JOIN CATEGORY AS C
ON ICM.CATEGORYID = C.CATEGORYID
Is there any way to return each ITEM once, with all CATEGORIES shown? My own knowledge of database theory say no, and that to do this, I would need several database calls - one for the ITEM list, then another call for each ITEM to get the category list. Am I missing something fundamental here? Received on Mon Apr 16 2007 - 12:54:42 CEST