Re: Many To Many Relationships


"Marshall" <marshall.spight@xxxxxxxxx> wrote in message
news:1176733370.109525.230310@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Apr 16, 6:47 am, "David Cressey" <cresse...@xxxxxxxxxxx> wrote:
"Loon" <skarl.the.gira...@xxxxxxxxx> wrote in message

news:1176720882.931603.161720@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



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.



.

Relevant Pages

  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Matching records for an update query
    ... then the update query would look something like ... UPDATE RegisteredMembers INNER JOIN BusinessChanges ...
    (microsoft.public.access.queries)
  • Re: Ranking query
    ... I expect that using a named query (as opposed to its SQL) will be OK. ... INNER JOIN qryRepairs AS I2 ...
    (microsoft.public.access.queries)