Re: Help for advanced SQL query

From: Randall Roberts <randall_at_filer.org.nospam>
Date: Mon, 28 May 2001 19:29:16 -0700
Message-ID: <3b13099e_1@news.pcmagic.net>

Yes, as Daniel said, you have to resolve a many to many relationship with a third table, which he and I call an intersection entity... but which I've heard called a "join table", "resolution table", and a couple of other things in my career.

You need

table OBJECT

---

object_id (PK)
name

table MAP

---

map_id (PK)
position
source

table CLUSTER

---

object_id (1st PK col, FK to OBJECT)
map_id (2nd PK col, FK to MAP)
cluster_id

Last October I did a presentation for the Los Angeles Oracle User's Group entitled "Isolating Applications in Oracle". If you go to www.laoug.org and find the speaker presentation library, you can download my PowerPoint presentation (the file is DBA_music.zip). It has a complete example of resolving a many to many using an intersection entity, including the SQL queries.

Best!

Randall

Daniel A. Morgan <dmorgan_at_exesolutions.com> wrote in message news:3B0F4E84.157F2E05_at_exesolutions.com...
> gene wrote:
>
> > I want to do a SQL query which is a little too advanced for my skills.
> >
> > For this query there are two tables:
> > table OBJECT
> > ------
> > object_id
> > cluster_id
> > name
> >
> > table MAP
> > -----
> > map_id
> > cluster_id
> > position
> > source
> >
> > cluster_id is a foreign key in both tables that I am using to join
> > OBJECT and MAP together. Most OBJECTS have one MAP, but this can be a
> > many to many relationship. Currently, I am doing this query to get my
> > mapping information:
> > select
> > OBJECT.name,
> > MAP.position,
> > MAP.source
> > from
> > OBJECT,
> > MAP
> > where
> > OBJECT.cluster_id = MAP.cluster_id;
> >
> > What I would like to do is to modify the query so that, in the cases
> > where there are multiple MAPs for one OBJECT, only one MAP per
> > position is returned. In other words, if there are three MAPs for a
> > given OBJECT with positions of 1.0, 2.0, and 2.0, then the 1.0 MAP and
> > either one, but not both, of the 2.0 MAPs are returned.
> >
> > Another option, is to return only one MAP per OBJECT, with the
> > position being the average of all the MAP positions for that object.
> > The more tricky part with that is that the other MAP fields, in this
> > example MAP.source, need to be combined as well, by concatenation of
> > the text entries.
> >
> > Is this too much to try to do in SQL?
> >
> > Thanks.
>
> It is far to late at night for me to think clearly and help you but I do
> want to comment on the fact that you have a Many-to-Many relationship
> between your tables: This is very bad design. You need to create a third
> table, an intersecting entity, with the primary keys from the other two.
>
> Hopefully someone wider awake will help you with your immediate query.
>
> Daniel A. Morgan
>
Received on Mon May 28 2001 - 21:29:16 CDT