Re: Many To Many Relationships

"Loon" <skarl.the.giraffe_at_gmail.com> wrote in message news: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
>
> 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?
>

Yes, there is a way of returning an ITEM, and then a list of all CATEGORIES that pertain. As to whether you're missing something fundamental here, I can't be sure.

What I will point out is this: The result you want is not a table as such. It's a hierarchy.

An SQL query typically returns a table. This, I believe, is the source of your difficulty. You will need a query to fetch the ITEM name, and another query to fetch the list of pertaining CATEGORIES. Each query will return a table, and you can present this in whatever form you like on the screen, or in your program.

However, if I wanted to do this, I'd proceed differently. Here's what I'd do.

I'd retrieve all the results I wanted in a single query, with one or two joins, as the case may be. The resulting table would be exactly what I want, except that the ITEM name would be repeated once in each row. If I were passing these results to an application program, I would just live with this. The inefficiency is negligible, and the benefits of dealing with the result in tabular form outweigh any inefficiencies.

If I were presenting text to view on the screen, or a text report, where repeating the ITEM name over and over would look awkward and possibly misleading, I would shove the results of the query through a hierarchical report writer (there are many of these). I would then ask the report writer to include the ITEM name only when it changes (or some such thing as that). Most report writers have such a feature.

And voila! Just what the users wanted!

The same discussion pertains to any other columns obtained from the same table as ITEM.

I'm sorry if this is a little vague, but I think you already know how to do the simple query in SQL, and different report writers have different ways of presenting hierarchical lists. Received on Mon Apr 16 2007 - 15:47:48 CEST