Re: Help with query

Many thanks Josh for the solution and the advice...

On Wed, 11 Aug 2004 21:48:50 -0700, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> 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
>