Re: Help for advanced SQL query

"gene" <usenet_at_smalltime.com> wrote in message news:9a95c0dd.0105311344.35eed2b7_at_posting.google.com...
> "Randall Roberts" <randall_at_filer.org.nospam> wrote in message
 news:<3b13099e_1_at_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.
> >
>
> Thanks for your replies. There actually is a third table (alluded to
> by the cluster_ids), though it seems different from the third table
> that you're describing. Here is a simplified description of the
> tables:
>
> table OBJECT
> ------
> object_id (PK)
> name
> cluster_id (FK)
>
> table MAP
> -----
> map_id (PK)
> position
> source
> cluster_id (FK)
>
> table CLUSTER
> -----
> cluster_id (PK)
> name
>
> If I were to create an additional table directly relating the maps and
> the objects:
>
> table OBJECT_MAP
> ------
> map_id (FK)
> object_id (FK)
>
> it would just be the same product of the two tables as I get by doing
> the join, or am I missing something?

i don't think you've missed anything. but just to clarify...

let's assume that we are talking about equi-joins, that is, joins that use an equals comparison operator...

the existing CLUSTER table will allow you to join rows from the MAP table to rows from OBJECT table where the values of the cluster_id column are equal.

if you were to add the new OBJECT_MAP table, as you show, it would be possible to add a row to "relate" a row from the MAP table to a row from the OBJECT table, without requiring that the rows have a matching value for cluster_id.

your OBJECT_MAP table is a classic example of a "third table" that resolves a many-to-many relationship. in some cases, there is add'l information carried on the relationship table.

so it really depends... do you want to require that the rows from OBJECT and the rows from MAP have a matching cluster_id ? in that case, you don't even really have a need to reference the CLUSTER table, just do a join where OBJECT.cluster_id = MAP.cluster_id.

if you need to relate OBJECT and MAP by some condition other than matching cluster_id, then you may need a table like OBJECT_MAP.

let me provide another example for you to consider:

an ORDER table, each row represents an order from a customer and a PRODUCT table, each row represents a specific product.

a customer may request several products on a single ORDER and a single PRODUCT may be requested on several orders

since there is a many-to-many relationship between the ORDER table and the PRODUCT table, we resolve this by adding a third table, typically named LINE_ITEM, which has a foreign key to the ORDER table and a foreign key to the PRODUCT table.

the LINE_ITEM table can also carry other useful information, like the number of units ordered, the unit price, and so on.

HTH Received on Thu May 31 2001 - 22:40:56 CDT