Many To Many Relationships

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 This results in multiples of ITEM where it is mapped to more than CATEGORY. My question is:
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 - 05:54:42 CDT