Re: Many To Many Relationships

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:1176733370.109525.230310_at_b75g2000hsg.googlegroups.com...
> 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
>

I never heard of an aggregate union operator. Sounds interesting. Although I think I understand how it works, could you provide a reference that describes it in more detail? Received on Tue Apr 17 2007 - 14:18:44 CEST