Re: Many To Many Relationships

On Apr 16, 6:47 am, "David Cressey" <cresse..._at_verizon.net> wrote:
> "Loon" <skarl.the.gira..._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.

As a theoretical response, we could consider that if we had an algebraic
relational language, we could do the query for all the items x the categories
for those items, and apply the aggregate union operator, grouped by item id.

  (items join categories join item_category_map) aggregate_union group by item_id

Something like that. Requires RVAs and an aggregate union. Clearly no standard way to do in SQL, but there might be some hack that lets you do the union as a string operation which might be sufficient for some applications.

Marshall Received on Mon Apr 16 2007 - 16:22:50 CEST