Re: Many To Many Relationships

Loon wrote:
> 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?

There is nothing fundamental about problem. You problem is more about understanding how SQL works...If you consider the three following tables
CATEGORIES, ITEM, ITEM_CATEGORY_MAP then you can get all information with

select C.*, I.* from CATEGORIES C
inner join ITEM_CATEGORY_MAP ICM

      on C.CATEGORYID = ICM.CATEGORYID
inner join ITEM I

      on ICM.ITEMID = I.ITEMID

the above should be ok under SQL Server....

Hope this helps... Received on Mon Apr 16 2007 - 14:36:33 CEST