Ryan,
> select c.user_id, a.user_id from athletes a, coaches c,
> coaching_relationships cr where a.athlete_id = cr.athlete_id and
> c.coach_id = cr.coach_id;
You're close:
select cuser.username as coach, auser.username as athlete
from athletes, coaches, coaching_relationships co_rel,
users cuser, users auser
where athletes.athlete_id = co_rel.athlete_id
and coaches.coach_id = co_rel.coach_id
and coaches.user_id = cuser.user_id
and athletes.user_id = auser.user_id
order by cuser.username, auser.username
And some unsolicited advice: don't abbreviate table names which are less than
10 characters. When you have to revisit these queries in 9 months, you won't
want to see all those "c" and "a" and "a1" tablename aliases. It's like
using programming variables named "x" and "y".
--
Josh Berkus
Aglio Database Solutions
San Francisco