[thelist] SQL Madness! Many-to-many selection

[rudy]

> If you need to store properties about the relationship,
> such as "date added", "who added it", ...  then an
> intermediary table is the only way to go.

it's the only way to go, period

you can't do many-to-many relationships without the relationship table,
also called a cross-reference table or linking table -- sometimes i'll
accidentally call it a "junction" table, a throwback to pre-relational
network database days...

as jay suggested, the intermediate table "has a one to many relationship to
each of the tables in the many to many relationship"

actually, from the relationship table's point of view, it's a many-to-one
relationship to each of the parent tables -- but let's not quibble, the
idea is right


> to not have to use thrice embedded queries and the alleviate some of
> my speed concerns.

sorry, joshua, but the three-way join with the subselect is the simplest

and simplest almost always translates into fastest

trust your dba, that he or she added the right indexes to your tables

and above all, trust your database engine's optimizer


rudy