Re: Help with query

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