partial JOIN (was: ID column naming convention)

Hi,

Recent interesting discussion on the list, on (just) naming convention
reminded me of a related problem which I haven't resolved myself, jet.

As slowly I learn SQL (like a blind dog in a meat market), currently
I've just started to use table JOINS more extensively ... and I often
bump on a "two columns have the same name" error.

The point is, that my "large object" often contain sets of (quite)
identical component objects; like: a SLED has LEFT_RUNNER and
RIGHT_RUNNER, both referring to the same RUNNERS table. And yes, I have
sattled with naming convention where table is a plural noun (table
RUNNERS), while primary key column name is an unspeciffic singular noun
(A_RUNNER).

So, when I join the SLEDS table with RUNNERS table (twice: left and
right runner, to get a complete bom for a sled):
SELECT * FROM sleds s JOIN runners l ON (s.left=l.runner) JOIN runners r
ON (s.right=r.runner);
.... it doesn't work, when SLED table and RUNNERS table both have the
same column (like LENGTH). And it wouldn't work even if I called
sled.length a SLED.SLED_LENGTH and runner.length a RUNNER.RUNNER_LENGTH,
since RUNNERS are joined twice.

Is there an sql-language level solution (idiom) to cope with such
queries? And I don't mean:
SELECT s.*,r.*,l.* FROM sleds s JOIN runners l ON (s.left=l.runner) JOIN
runners r ON (s.right=r.runner);
...since in such case, there would be even more duplicate column names
then in the first example. The only way I know to avoid the column name
duplication is to explicity select column list:
SELECT s.sled,s.length,s....,r.runner as right,r.length as
right_length,r....,l.runner as left,l.length as left_length,l.* FROM
sleds s JOIN runners l ON (s.left=l.runner) JOIN runners r ON
(s.right=r.runner);
.... which is truely overtalkative (and thus obfuscates future query
analize during code maintenance).

To explain the problem a little better, here is a "pseudoSQL" query
example, which should ilustrate the problem/solution:
SELECT * FROM sleds s JOIN runners(length as left_length, weight as
left_weight, runner as left) l USING (left) JOIN runners(length as
right_length, weight as right_weight, runner as right) r USING (right);

I found one way to get "almost exactly" to this point, that is by
createing VIEWs to do the job of renameing columns:
CREATE VIEW right_runner AS SELECT length as right_length, weight as
right_weight, runner as right FROM runners;
CREATE VIEW left_runner AS SELECT length as left_length, weight as
right_weight, runner as left FROM runners;
.... one problem with this solution is that during the livetime of an
application, columns of tables (like RUNNERS) change (like by adding new
attributes), and it's quite tricky to promote those changes smoothly up
to the top "SELECT * FROM sleds ...", particularly if that last one is
actually a CREATED VIEW.
... the other problem is that it exploads application/db schema, and
thus make it more difficult to "comprehend" during future maintenance.

A real live example of such query is quering addreses of a person:
residential_address, office_address, delivery_address, etc... all of
them from a single ADDRESSES table. Or people telephone numbers from
PHONE_NUMERS table.

I have seen the WINDOW-PARTITION-OVER syntax very similar to this
problem, but countrary to JOINS, WINDOW can be defined
outside of a select column list, which is helpfull. I havent found such
construct for JOINs. Is there any?

Is there an ordinary SQL phrase/idiom (laconic/tarse in it's form) to
get the above result?

-R