Re: Many To Many Relationships

From: David Cressey <cressey73_at_verizon.net>
Date: Mon, 16 Apr 2007 20:20:10 GMT
Message-ID: <_TQUh.2474$BS2.2306_at_trndny01>


"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.
>
OK, but what I offered was not a hack. For once, I was trying to give a serious response to this kind of question.

(You may not recognize it as such, but it's the same question that Dawn has raised a dozen times in the last few years.)

Pushing the result of a SQL query through a hierarchical report writer is really not hacking. It's serious work, albeit without much theoretical merit at all. Received on Mon Apr 16 2007 - 22:20:10 CEST

Original text of this message